Re: [PERFORM] Strange explain on partitioned tables

2010-08-04 Thread Robert Haas
On Mon, Jul 26, 2010 at 5:26 PM, Gerald Fontenay wrote: > Thank you for your response. So if I query only my target child table, this > should be "just like" any other single table wrt planning right? I have > thousands of these tables. (I suppose that I'm only using inheritance for > the sake of

Re: [PERFORM] Strange explain on partitioned tables

2010-07-26 Thread Gerald Fontenay
Joshua D. Drake wrote: On Fri, 2010-07-23 at 15:03 -0700, Gerald Fontenay wrote: The PostgreSQL partitioning system is aimed to support perhaps a hundred inherited tables. You can expect to get poor performance on queries if you create 1000 of them. Hi, Why is that you would expec

Re: [PERFORM] Strange explain on partitioned tables

2010-07-23 Thread Gerald Fontenay
Joshua D. Drake wrote: On Fri, 2010-07-23 at 15:03 -0700, Gerald Fontenay wrote: The PostgreSQL partitioning system is aimed to support perhaps a hundred inherited tables. You can expect to get poor performance on queries if you create 1000 of them. Hi, Why is that you would expec

Re: [PERFORM] Strange explain on partitioned tables

2010-07-23 Thread Greg Smith
Gerald Fontenay wrote: The PostgreSQL partitioning system is aimed to support perhaps a hundred inherited tables. You can expect to get poor performance on queries if you create 1000 of them. Why is that you would expect poor performance for say 1000 or more? When the query planner execu

Re: [PERFORM] Strange explain on partitioned tables

2010-07-23 Thread Joshua D. Drake
On Fri, 2010-07-23 at 15:03 -0700, Gerald Fontenay wrote: > > The PostgreSQL partitioning system is aimed to support perhaps a > > hundred inherited tables. You can expect to get poor performance on > > queries if you create 1000 of them. > > Hi, > > Why is that you would expect poor performa

Re: [PERFORM] Strange explain on partitioned tables

2010-07-23 Thread Gerald Fontenay
The PostgreSQL partitioning system is aimed to support perhaps a hundred inherited tables. You can expect to get poor performance on queries if you create 1000 of them. Hi, Why is that you would expect poor performance for say 1000 or more? I have a ~1000 inherited tables and I don't see

Re: [PERFORM] Strange explain on partitioned tables

2010-07-22 Thread Philippe Rimbault
Greg, First : thank you for you help. On 22/07/2010 15:32, Greg Smith wrote: Philippe Rimbault wrote: I have one thousand inherited tables like this one (with a different check constraint on each) : The PostgreSQL partitioning system is aimed to support perhaps a hundred inherited tables.

Re: [PERFORM] Strange explain on partitioned tables

2010-07-22 Thread Greg Smith
Philippe Rimbault wrote: I have one thousand inherited tables like this one (with a different check constraint on each) : The PostgreSQL partitioning system is aimed to support perhaps a hundred inherited tables. You can expect to get poor performance on queries if you create 1000 of them.

Re: [PERFORM] Strange explain on partitioned tables

2010-07-22 Thread Philippe Rimbault
FYI I've just installed Postgresql 9 beta 3 (9.0beta3 on i686-pc-linux-gnu, compiled by GCC gcc (Debian 4.4.4-6) 4.4.4, 32-bit) After a pg_upgrade + vacuum analyze, i've got the following results : Query on primary table : select documents.id, documents.num, sources.n

Re: [PERFORM] Strange explain on partitioned tables

2010-07-22 Thread Philippe Rimbault
Oups! searching on the mailing list show me that it's a known problem ... http://archives.postgresql.org/pgsql-performance/2010-07/msg00063.php sorry ! On 22/07/2010 09:52, Philippe Rimbault wrote: Hi all, I'm using Postgresql 8.4.4 on Debian. In postgresql.conf, constraint_exclusion is se

[PERFORM] Strange explain on partitioned tables

2010-07-22 Thread Philippe Rimbault
Hi all, I'm using Postgresql 8.4.4 on Debian. In postgresql.conf, constraint_exclusion is set to "on" I have partitioned tables with check constraints. My primary table : CREATE TABLE documents ( id serial NOT NULL, id_source smallint, nod integer, num te