PostgreSQL query optimization, reading EXPLAIN

Question

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

Note that individual rows in that are cheap (0.013ms) BUT there's 870k loops on that table. Have you tried creating an index on date_created and / or chunks? — Scott Marlowe
– Scott Marlowe, Commented Aug 24, 2017 at 17:20
Note that the row size is rather large (~1K) on the upper few layers of the plan. (and the hash table could be spilling to disk). (BTW: please edit your query to something readable ...) — wildplasser
– wildplasser, Commented Aug 24, 2017 at 19:04
Can you double check that the query you posted corresponds to the execution plan? There are some things that aren't matching up, i.e. there's there's no partition_date filters in your execution plan but they are in the where clause and your parser in the execution plan is looking for educ whereas your query shows ('english', 'state'). Anyhow, my hunch is that the query is not tied to the SELECT 4 execution plan. The execution plan actually looks like there might be a rogue join on the posts table twice. — DoDoSmarts
– DoDoSmarts, Commented Aug 25, 2017 at 12:17

Łukasz Kamiński · Accepted Answer · 2017-08-25 12:40:26Z

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

Collectives™ on Stack Overflow

PostgreSQL query optimization, reading EXPLAIN

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related