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/



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

"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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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/



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

>>>> 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/



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster