CODEWITHSUNDEEP
sqlpostgresqlquery-optimization
maxTrialfire
maxTrialfire

Reputation: 551

update with join on CTE doesn't use index, but with temp table it does

Using postgres9.6. All statistics up to date, everything vacuumed and repacked.

We all know about 10% of the internet is devoted to "why isnt postgres using my index"....this is one of those

I have a table session, with index on primary key id. When I do the following update its slow (5+ minutes)

WITH session_cte as (

  --big complex cte, however the cte itself is FAST

)
update session S set S.foo = Z.foo
from session_cte as Z
where Z.id = S.id

Explain seen here: https://explain.depesz.com/s/FtI4 The culprit is the seq_scan at the bottom.

However when instead of a CTE I dump the result into a temp table and then join on that for the update its blazing fast, explain here: https://explain.depesz.com/s/W3c6

create temp table session_cte_temp AS ( 
   --same complicated SQL as in the CTE
 );
 update session S set S.foo = Z.foo
 from session_cte as Z
 where Z.id = S.id;

Can someone explain why the cte is tripping up the planner so badly? Why in the temp table case the correct index is chosen?

Upvotes: 3

Views: 830

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246688

You didn't say it in so many words, but it looks like you ran ANALYZE on the temporary table, because the estimate of 1 row is spot an.

That makes the difference.

PostgreSQL runs the same query, regardless if it is a CTE or creates a temporary table, and that query is fast, as you correctly observe.

Unfortunately PostgreSQL's estimate of the number of rows found by that subquery is horribly wrong — I'll analyze (hint, hint!) the cause later on.

This wrong estimate is corrected by the creation of the temporary table and particularly the ANALYZE, so you end up with a good execution plan.

But let's take a step back.

Why is the misestimate so bad? The reason are the misestimates of the scans on alias_staging_zml and session_fbc669c0_3cce_4322_83a6_8b80da7ed545. So you should ANALYZE these tables, and that should change everything else.

If ANALYZE doesn't improve things, you might have to increase the statistics target for the column, e.g.

ALTER TABLE session_fbc669c0_3cce_4322_83a6_8b80da7ed545
   ALTER person_id SET STATISTICS 1000;

Upvotes: 3

Related Questions