Thread: select max(column) from parent table very slow

select max(column) from parent table very slow

From
"Sriram Dandapani"
Date:

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

Re: select max(column) from parent table very slow

From
"Joshua D. Drake"
Date:
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/



Re: select max(column) from parent table very slow

From
Tom Lane
Date:
"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

Re: select max(column) from parent table very slow

From
"Joshua D. Drake"
Date:
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/



Re: select max(column) from parent table very slow

From
Alvaro Herrera
Date:
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

Re: select max(column) from parent table very slow

From
"Joshua D. Drake"
Date:
>>>> 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/