PostgreSQL Administration - Question on partitioning

Author

Question on partitioning

Mark Steben

2006-11-15, 7:34 pm

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 & #91;mailto:msteben@a
utorevenue.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

Simon Riggs

2006-11-16, 7:16 pm

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/...TIONING-CAVEATS

especially the ones about cross-datatype comparisons in CHECK
constraints.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Mark Steben

2006-11-16, 7:16 pm

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 & #91;mailto:simon@2nd
quadrant.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/...ml#DDL-PARTITIO
NING-CAVEATS

especially the ones about cross-datatype comparisons in CHECK
constraints.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Sponsored Links