On Oct 30, 2007, at 9:31 AM, Michael Glaesemann wrote:
On Oct 30, 2007, at 7:18 , Ketema wrote:
here is the execution plan:
I've put this online here:
http://explain-analyze.info/query_plans/1259-ketema-2007-10-30
I have attached an erd of the tables used in this query. If it is
stripped out it can be viewed here: http://www.ketema.net/
provision_list_tables_erd.jpg
My concern is with the sort step that takes 15 seconds by itself:
-> Sort (cost=1235567017.53..1238002161.29 rows=974057502
width=290)
(actual time=16576.997..16577.513 rows=3366 loops=1)
What jumps out at me is the huge difference in estimated and
returned rows, and the huge cost estimates. Have you analyzed
recently?
Yes. I run vacuum FULL ANALYZE VERBOSE every two days with a cron job.
I am running again now any way.
Do you have enable_seqscan disabled? It appears so, due to the high
cost here:
-> Seq Scan on order_details (cost=100000000.0..100000012.45
rows=35 width=199) (actual time=0.001..0.001 rows=0 loops=1)
http://explain-analyze.info/query_plans/1259-
ketema-2007-10-30#node-3594
What does it look like with seqscan enabled?
it was disabled. new plan posted here:
http://explain-analyze.info/query_plans/1261-provision-list-seq-scan-
enabled
2)Create Views of the counts and the sub select...is this any faster
as the view is executed at run time anyway?
Views aren't materialized: it's like inlining the definition of the
view itself in the query.
3)Create actual tables of the sub select and aggregates...How would
this be maintained to ensure it was always accurate?
One way would be to update the summaries using triggers. Hopefully
you won't need to do this after analyzing and perhaps tweaking your
server configuration.
Unfortunately I don't have the time to look at the query plan in
more detail, but I suspect there's a better way to get the results
you're looking for.
Michael Glaesemann
grzm seespotcode net
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
