From: Richard Huxton <dev@archonet.com>
To: Ketema <ketema@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Improving Query
Date: Tue, 30 Oct 2007 13:23:39 +0000
Message-ID: <4727305B.9050307@archonet.com> (raw)
In-Reply-To: <1193746737.222960.206150@50g2000hsm.googlegroups.com>
References: <1193746737.222960.206150@50g2000hsm.googlegroups.com>
Ketema wrote:
> I have the following query that is a part of a function:
Yikes! Difficult to get a clear view of what this query is doing.
OK, I'm assuming you're vacuumed and analysed on all these tables...
> 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)
That's taking hardly any time, the startup time is 16576.997 already. Of
course, the row estimate is *way* out of line.
If you look here (where the explain is a bit easier to see)
http://explain-analyze.info/query_plans/1258-query-plan-224
The two main things to look at seem to be the nested loops near the top
and a few lines down the materialise (cost=326...
These two nested loops seem to be pushing the row estimates wildly out
of reality. They also consume much of the time.
The immediate thing that leaps out here is that you are trying to join
an int to an array of ints. Why are you using this setup rather than a
separate table?
> How can I improve this step?
>
> Things I have thought about:
> 1)Creating indexes on the aggregates...Found out this can't be done.
Nope - not sure what it would mean in any case.
> 2)Create Views of the counts and the sub select...is this any faster
> as the view is executed at run time anyway?
Might make the query easier to write, won't make it faster. Not without
materialised views which are the fancy name for #3...
> 3)Create actual tables of the sub select and aggregates...How would
> this be maintained to ensure it was always accurate?
Triggers.
> 4)Increasing hardware resources. Currently box is on a single
> processor amd64 with 8Gb of RAM. below are the settings for resource
> usage.
> shared_buffers = 65536
> temp_buffers = 5000
> max_prepared_transactions = 2000
????
> work_mem = 131072
> maintenance_work_mem = 512000
Can't say about these without knowing whether you've got only one
connection or 100.
> max_stack_depth = 7168
> max_fsm_pages = 160000
> max_fsm_relations = 4000
> The only function of this box if for Pg, so I do not mind it using
> every last drop of ram and resources that it can.
> 5)Upgrade version of pg..currently is running 8.1.4
Well every version gets better at planning, so it can't hurt.
--
Richard Huxton
Archonet Ltd
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dev@archonet.com, ketema@gmail.com
Subject: Re: Improving Query
In-Reply-To: <4727305B.9050307@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox