Thread: select max(column) from parent table very slow
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
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/
"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
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/
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
>>>> 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/