PostgreSQL Administration - Question on partitioning

Author

Question on partitioning

Mark Steben

2006-11-10, 7:24 pm

Thanks to all who responded to my question on querying on cluster index
names.

I have another question. We are considering partitioning 2 or 3 of our
larger tables.

Each has 12 million or more rows. We want to partition on customer_id and
build

Primary key indexes on each of the children partitions which inherit column
characteristics

From the master table. This would resolve any issues about queries on
customer-id.

My question is this: How would I handle a query that queries

On another Non-partitioned column, say, ENTRY_DATE? Could I design an index

On the master table that would reference ENTRY-DATE across partitions or
will

I need to build indexes and queries on each partition?

Thank you for your time,

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

Richard Broersma Jr

2006-11-10, 7:24 pm

> Thanks to all who responded to my question on querying on cluster index

> names.
> I have another question. We are considering partitioning 2 or 3 of our
> larger tables.
> Each has 12 million or more rows. We want to partition on customer_id and
> build
> Primary key indexes on each of the children partitions which inherit column
> characteristics
> From the master table. This would resolve any issues about queries on
> customer-id.
> My question is this: How would I handle a query that queries
> On another Non-partitioned column, say, ENTRY_DATE? Could I design an index
> On the master table that would reference ENTRY-DATE across partitions or
> will
> I need to build indexes and queries on each partition?

I haven't done too much partitioning yet, but this is where I would start looking first:
http://www.postgresql.org/docs/8.1/...rtitioning.html

Regards,

Richard Broersma Jr.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Joshua D. Drake

2006-11-10, 7:24 pm

[color=darkred]

Indexes yes, queries no. You use a check constraint on the child tables
to allow the planner to determine which partitions will be used in a
particular query.

Sincerely,

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/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Sponsored Links