CODEWITHSUNDEEP
postgresqlsql-execution-plan
Mark
Mark

Reputation: 140

PostgreSQL query optimization, reading EXPLAIN

I'm reading the output of running an explain on a query. This is the result: https://explain.depesz.com/s/5EfR

I see that on row #34, the DB is doing a very expensive index scan that results in removing a single row.

Am I reading this correctly? Also, ideas what could be causing this?

query:

explain analyze select *, posts.type as type, posts.created as created, posts.image_url as image_url, posts.id as post_id, posts.organization_id as id,
    urls.image_url as url_image_url,
    ts_headline('english',posts.text , to_tsquery('english', 'state')  , $$StartSel='<span class="text-highlight">',StopSel=</span>, HighlightAll=true$$) as text,
    ts_headline('english',posts.attachment_description, to_tsquery('english', 'state')  , $$StartSel='<span class="text-highlight">',StopSel=</span>, HighlightAll=true$$) as attachment_description,
    ts_headline('english',posts.attachment_title, to_tsquery('english', 'state')  , $$StartSel='<span class="text-highlight">',StopSel=</span>, HighlightAll=true$$) as attachment_title
     from vision2.posts15 as posts join vision2.organizations on organizations.id=posts.organization_id left join vision2.urls on urls.id = posts.url_id where  chunks @@ to_tsquery('english', 'state')   and string_to_array(upper(organizations.irs_state), '') && array['NJ']  and Date(posts.created) >= '2017-08-10' and Date(posts.created) <= '2017-08-24' and Date(posts.partition_date) >= '2017-08-10' and Date(posts.partition_date) <= '2017-08-24'  order by posts.created desc   offset 0 limit 40

Upvotes: 0

Views: 74

Answers (1)

Łukasz Kamiński
Łukasz Kamiński

Reputation: 5930

Try to limit data before you do joins. You can use CTE for that since they are materialized once and work like optimization fence or temp table if you like.

So your query could look like this:

WITH cte_posts AS (
  select type, created, image_url, id as post_id, organization_id as id, url_id,
         ts_headline('english',
                     text,
                     to_tsquery('english', 'state'),
                     $$StartSel='<span class="text-highlight">',StopSel=</span>, HighlightAll=true$$) as text,
         ts_headline('english',
                     attachment_description,
                     to_tsquery('english', 'state'),
                     $$StartSel='<span class="text-highlight">',StopSel=</span>, HighlightAll=true$$) as attachment_description,
         ts_headline('english',
                     attachment_title,
                     to_tsquery('english', 'state'),
                     $$StartSel='<span class="text-highlight">',StopSel=</span>, HighlightAll=true$$) as attachment_title
  from vision2.posts15
  where Date(created) BETWEEN '2017-08-10' AND '2017-08-24'
  and Date(partition_date) BETWEEN '2017-08-10' AND '2017-08-24'
  AND chunks @@ to_tsquery('english', 'state') --is that column in posts15 table?
)
SELECT cte_posts.*, urls.image_url as url_image_url
FROM cte_posts
join vision2.organizations on organizations.id = cte_posts.id
left join vision2.urls on urls.id = cte_posts.url_id
      --you could try moving this WHERE to CTE as well, depending on your data
where string_to_array(upper(organizations.irs_state), '') && array['NJ']
order by cte_posts.created desc
offset 0
limit 40

Upvotes: 1

Related Questions