Postgres Pro
Downloads
Home   >   mailing lists

Re: Very slow query performance when using CTE - Mailing list pgsql-performance

From Michael Christofides
Subject Re: Very slow query performance when using CTE
Date
Msg-id CAFwT4nBzs=iLHg5Q75rA=6AtA=AqqutOS051XzBw3XFt90d1_Q@mail.gmail.com
Whole thread Raw
In response to Fwd: Very slow query performance when using CTE  (Chris Joysn <joysn71@gmail.com>)
Responses Re: Very slow query performance when using CTE
List pgsql-performance
Tree view
CREATE STATISTICS st_simrun_component_metadata (dependencies) ON sim_run_id, key FROM sim_run_component_metadata;
ANALYZE sim_run_component_metadata;

When I run this query, no statistics are returned:

SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid),

pg_mcv_list_items(stxdmcv) m WHERE stxname = 'st_simrun_component_metadata';


Is there something I might have missed?

It looks like you created "dependencies" statistics, but then searched for "mcv" statistics. To test if mcv helps, you could drop and recreate as: CREATE STATISTICS st_simrun_component_metadata (mcv) ... 

The fetch from the table is rather fast. some milliseconds. But a subsequent sort operations takes very long time, for the amount of records fetched.

This does not seem to be the case for the slow cases you shared (those are dominated by several millisecond index scans that are looped over 32k times). So I assume you're talking about the fast case? If so, there is a Sort that takes a couple of hundred milliseconds being done on disk (~15MB) so you might also want to look into how fast that would be in memory (via work_mem).
 
But, just like the estimated rows in the plan, it does not match the real amount of available data in the table:

I'm not sure what you mean by this, is it only that the row estimates are still bad?

Regards,
Michael

pgsql-performance by date:

Previous
From: Chris Joysn
Date:
Subject: Fwd: Very slow query performance when using CTE
Next
From: James Pang
Date:
Subject: Re: partition table optimizer join cost misestimation