Re: Improving Query - Mailing list pgsql-performance

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