Обсуждение: Question on partitioning
Hi, the new guy is back with another question.
Regarding partitioning – I set up a testing table – parent table and 4 partitioning children’s tables inheriting the
Attributes of the parent. I inserted 1800 rows in all – 450 equitably in each partition. The CHECK constraints work great.
The PART_ID column, defined as SMALLINT, Is the partitioning column.
When I query for PART_ID’S I know to be in the first partition only, the EXPLAIN says that
The query plan says that it does a sequential table scan on the first partition, which is fine because I do SELECT *. However it also
Does index scans on partitions 2, 3, and 4 which I did not expect since I limited the range in my WHERE clause to rows in partition 1.
Do I need to load more data? Any help would be appreciated. I did set my CONSTRAINT_EXCLUSION to ON.
Thank you,
Mark Steben
From: Mark Steben [mailto:msteben@autorevenue.com]
Sent: Tuesday, November 07, 2006 3:18 PM
To: 'pgsql-admin@postgresql.org'
Subject: Question
I am very new to PostgreSQL. Is it appropriate to pose questions to this email list?
I am trying to come up with a query that will list the names of the database indexes that
Have been chosen as clustering indexes. I know I can get the INDEXRELID from PG.INDEX
But have yet to figure out how to get the index name from there. Any help would be appreciated.
And, if this is not an appropriate forum to ask questions please tell me.
Thank you,
Mark Steben
AutoRevenue
On Wed, 2006-11-15 at 15:13 -0500, Mark Steben wrote: > Regarding partitioning – I set up a testing table – parent table and 4 > partitioning children’s tables inheriting the > > Attributes of the parent. I inserted 1800 rows in all – 450 equitably > in each partition. The CHECK constraints work great. > > The PART_ID column, defined as SMALLINT, Is the partitioning > column. > You'll probably want to look at the caveats here http://www.postgresql.org/docs/8.2/static/ddl-partitioning.html#DDL-PARTITIONING-CAVEATS especially the ones about cross-datatype comparisons in CHECK constraints. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Simon, you're right on the money.. the guys on the IRC chatroom suggested that I cast the partitioning column as SMALLINT as the optimizer assumed Integer and it worked. Thx for getting back to me Mark Steben AutoRevenue -----Original Message----- From: Simon Riggs [mailto:simon@2ndquadrant.com] Sent: Thursday, November 16, 2006 12:39 PM To: Mark Steben Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Question on partitioning On Wed, 2006-11-15 at 15:13 -0500, Mark Steben wrote: > Regarding partitioning - I set up a testing table - parent table and 4 > partitioning children's tables inheriting the > > Attributes of the parent. I inserted 1800 rows in all - 450 equitably > in each partition. The CHECK constraints work great. > > The PART_ID column, defined as SMALLINT, Is the partitioning > column. > You'll probably want to look at the caveats here http://www.postgresql.org/docs/8.2/static/ddl-partitioning.html#DDL-PARTITIO NING-CAVEATS especially the ones about cross-datatype comparisons in CHECK constraints. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com