Skip site navigation (1) Skip section navigation (2)

Re: Order by (for 15 rows) adds 30 seconds to query time

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: matthew(at)flymine(dot)org, jmpoure(at)free(dot)fr, pgsql-performance(at)postgresql(dot)org, craig(at)postnewspapers(dot)com(dot)au
Subject: Re: Order by (for 15 rows) adds 30 seconds to query time
Date: 2009-12-28 17:41:08
Message-ID: 12520.1262022068@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> The approach contemplated in the comment, of assembling some stats
>> on-the-fly from the stats for individual child tables, doesn't
>> seem real practical from a planning-time standpoint.
 
> Can you give a thumbnail sketch of why that is?

Well, it would be expensive, and it's not even clear you can do it at
all (merging histograms with overlapping bins seems like a mess for
instance).

I think we have previously discussed the idea of generating and storing
ANALYZE stats for a whole inheritance tree, which'd solve the problem
nicely from the planner's standpoint.  I'm a bit worried about the
locking implications, but if it took just a SELECT lock on the child
tables it probably wouldn't be too bad --- no worse than any other
SELECT on the inheritance tree.  Another thing that's hard to figure out
is how autovacuum would know when to redo the stats.  In a lot of common
situations, the inheritance parent table is empty and never changes, so
no autovac or autoanalyze would ever get launched against it.

			regards, tom lane

In response to

pgsql-performance by date

Next:From: Craig JamesDate: 2009-12-28 18:37:12
Subject: Re: SATA drives performance
Previous:From: Kevin GrittnerDate: 2009-12-28 15:09:26
Subject: Re: Order by (for 15 rows) adds 30 seconds to query time

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group