Обсуждение: select max(parent_table) slow
We are testing 8.4 and noticed that the performance of select max(indexed_col) from parent_Table is the same (slow) as postgres 8.1.x ( our current version). The child tables are huge and the indexed_col is not the basis for partitioning. I remember that this was an issue from an earlier thread where the optimizer was not able to use the child partitions only to get the max value. Is this true for 8.4 ?
On Tue, Aug 18, 2009 at 06:30:15AM -0700, Anj Adu wrote: > We are testing 8.4 and noticed that the performance of > > select max(indexed_col) from parent_Table > > is the same (slow) as postgres 8.1.x ( our current version). The > child tables are huge and the indexed_col is not the basis for > partitioning. > > I remember that this was an issue from an earlier thread where the > optimizer was not able to use the child partitions only to get the max > value. Is this true for 8.4 ? > This is still a problem as of 8.4. Regards, Ken
What is your constraint_exclusion config set to on your new instance? -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Anj Adu Sent: Tuesday, August 18, 2009 8:30 AM To: pgsql-admin@postgresql.org Subject: [ADMIN] select max(parent_table) slow We are testing 8.4 and noticed that the performance of select max(indexed_col) from parent_Table is the same (slow) as postgres 8.1.x ( our current version). The child tables are huge and the indexed_col is not the basis for partitioning. I remember that this was an issue from an earlier thread where the optimizer was not able to use the child partitions only to get the max value. Is this true for 8.4 ? -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
On Tue, Aug 18, 2009 at 08:38:28AM -0500, Plugge, Joe R. wrote: > What is your constraint_exclusion config set to on your new instance? > Even with it set to the default of "partition", the query does not push the max() down to the child tables and does a sequential scan of the tables and does not use the indexes that are present. There has been a recent thread on hackers about how to make that work. It does not yet and requires the application to break the queries up by the child tables to get reasonable performance. Regards, Ken > -----Original Message----- > From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Anj Adu > Sent: Tuesday, August 18, 2009 8:30 AM > To: pgsql-admin@postgresql.org > Subject: [ADMIN] select max(parent_table) slow > > We are testing 8.4 and noticed that the performance of > > select max(indexed_col) from parent_Table > > is the same (slow) as postgres 8.1.x ( our current version). The > child tables are huge and the indexed_col is not the basis for > partitioning. > > I remember that this was an issue from an earlier thread where the > optimizer was not able to use the child partitions only to get the max > value. Is this true for 8.4 ? > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin >