Re: [PERFORM] select max(column) from parent table very slow
Joshua D. Drake wrote: Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] 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 Herrerahttp://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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] select max(column) from parent table very slow
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
[PERFORM] 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
Re: [PERFORM] select max(column) from parent table very slow
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
Re: [PERFORM] select max(column) from parent table very slow
Joshua D. Drake [EMAIL PROTECTED] 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
Re: [PERFORM] select max(column) from parent table very slow
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
