pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedselect max(column) from parent table very slow
6+ messages / 4 participants
[nested] [flat]
* select max(column) from parent table very slow
@ 2006-08-24 23:43 Sriram Dandapani <sdandapani@counterpane.com>
0 siblings, 1 reply; 6+ messages in thread
From: Sriram Dandapani @ 2006-08-24 23:43 UTC (permalink / raw)
To: pgsql-performance
Parent table has a column say column1 which is indexed (parent table and
all child tables are indexed on that column)
When a select max(column1) is done on parent table..takes a very long
time to get back with the result
The same query on a child table gives instantaneous response (the tables
are quite large appx.each child table has about 20-30 million rows)
Constraint exclusion is turned on. The column is not the basis for
partitioning. Postgres 8.1.2
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: select max(column) from parent table very slow
@ 2006-08-25 01:08 Joshua D. Drake <jd@commandprompt.com>
parent: Sriram Dandapani <sdandapani@counterpane.com>
0 siblings, 1 reply; 6+ messages in thread
From: Joshua D. Drake @ 2006-08-25 01:08 UTC (permalink / raw)
To: Sriram Dandapani <sdandapani@counterpane.com>; +Cc: pgsql-performance
Sriram Dandapani wrote:
> Parent table has a column say column1 which is indexed (parent table and
> all child tables are indexed on that column)
>
Do you mean?
select max(foo) from bar;
In older versions of postgresql that would scan the whole table. In 8.1
and above it doesn't. However, I am guess that since this is a
partitioned table the planner isn't smart enough to just perform the
query on each child and a max on the set that is returned. Thus you are
scanning each table completely.
But that is just a guess.
Joshua D. Drake
>
>
> When a select max(column1) is done on parent table..takes a very long
> time to get back with the result
>
> The same query on a child table gives instantaneous response (the tables
> are quite large appx.each child table has about 20-30 million rows)
>
>
>
> Constraint exclusion is turned on. The column is not the basis for
> partitioning. Postgres 8.1.2
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: select max(column) from parent table very slow
@ 2006-08-25 04:33 Tom Lane <tgl@sss.pgh.pa.us>
parent: Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2006-08-25 04:33 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Sriram Dandapani <sdandapani@counterpane.com>; pgsql-performance
"Joshua D. Drake" <jd@commandprompt.com> writes:
> Sriram Dandapani wrote:
>> Parent table has a column say column1 which is indexed (parent table and
>> all child tables are indexed on that column)
> In older versions of postgresql that would scan the whole table. In 8.1
> and above it doesn't. However, I am guess that since this is a
> partitioned table the planner isn't smart enough to just perform the
> query on each child and a max on the set that is returned.
It is not. Feel free to submit a patch for planagg.c ...
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: select max(column) from parent table very slow
@ 2006-08-25 05:16 Joshua D. Drake <jd@commandprompt.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Joshua D. Drake @ 2006-08-25 05:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Sriram Dandapani <sdandapani@counterpane.com>; pgsql-performance
Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>> Sriram Dandapani wrote:
>>> Parent table has a column say column1 which is indexed (parent table and
>>> all child tables are indexed on that column)
>
>> In older versions of postgresql that would scan the whole table. In 8.1
>> and above it doesn't. However, I am guess that since this is a
>> partitioned table the planner isn't smart enough to just perform the
>> query on each child and a max on the set that is returned.
>
> It is not. Feel free to submit a patch for planagg.c ...
I think my patch to pgbench may have set your expectations of me a bit
high ;)...
Joshua D. Drake
>
> regards, tom lane
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: select max(column) from parent table very slow
@ 2006-08-25 14:45 Alvaro Herrera <alvherre@commandprompt.com>
parent: Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 6+ messages in thread
From: Alvaro Herrera @ 2006-08-25 14:45 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Sriram Dandapani <sdandapani@counterpane.com>; pgsql-performance
Joshua D. Drake wrote:
> Tom Lane wrote:
> >"Joshua D. Drake" <jd@commandprompt.com> writes:
> >>Sriram Dandapani wrote:
> >>>Parent table has a column say column1 which is indexed (parent table and
> >>>all child tables are indexed on that column)
> >
> >>In older versions of postgresql that would scan the whole table. In 8.1
> >>and above it doesn't. However, I am guess that since this is a
> >>partitioned table the planner isn't smart enough to just perform the
> >>query on each child and a max on the set that is returned.
> >
> >It is not. Feel free to submit a patch for planagg.c ...
>
> I think my patch to pgbench may have set your expectations of me a bit
> high ;)...
Actually I think this is the perfect opportunity for you -- a patch that
not only was absolutely unexpected, undiscussed, and posted without
previous warning, but one that you were actually asked about! And
weren't you recently joking about giving Tom nightmares by sending
patches to the optimizer?
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: select max(column) from parent table very slow
@ 2006-08-25 15:23 Joshua D. Drake <jd@commandprompt.com>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 6+ messages in thread
From: Joshua D. Drake @ 2006-08-25 15:23 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; Tom Lane <tgl@sss.pgh.pa.us>; Sriram Dandapani <sdandapani@counterpane.com>; pgsql-performance
>>>> query on each child and a max on the set that is returned.
>>> It is not. Feel free to submit a patch for planagg.c ...
>> I think my patch to pgbench may have set your expectations of me a bit
>> high ;)...
>
> Actually I think this is the perfect opportunity for you -- a patch that
> not only was absolutely unexpected, undiscussed, and posted without
> previous warning, but one that you were actually asked about! And
> weren't you recently joking about giving Tom nightmares by sending
> patches to the optimizer?
Yeah, but Tom is getting up there a bit, and that might mean a heart
attack. Then what would we do? ;)
Joshua D. Drake
>
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-08-25 15:23 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-24 23:43 select max(column) from parent table very slow Sriram Dandapani <sdandapani@counterpane.com>
2006-08-25 01:08 ` Joshua D. Drake <jd@commandprompt.com>
2006-08-25 04:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-08-25 05:16 ` Joshua D. Drake <jd@commandprompt.com>
2006-08-25 14:45 ` Alvaro Herrera <alvherre@commandprompt.com>
2006-08-25 15:23 ` Joshua D. Drake <jd@commandprompt.com>
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