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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Craig James | 2009-12-28 18:37:12 | Re: SATA drives performance |
Previous Message | Kevin Grittner | 2009-12-28 15:09:26 | Re: Order by (for 15 rows) adds 30 seconds to query time |