pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion on partitioning
6+ messages / 4 participants
[nested] [flat]
* Question on partitioning
@ 2006-11-10 16:29 Mark Steben <msteben@autorevenue.com>
2006-11-10 17:39 ` Re: Question on partitioning Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 1 reply; 6+ messages in thread
From: Mark Steben @ 2006-11-10 16:29 UTC (permalink / raw)
To: 'Mark Steben' <msteben@autorevenue.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Question on partitioning
2006-11-10 16:29 Question on partitioning Mark Steben <msteben@autorevenue.com>
@ 2006-11-10 17:39 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-11-10 18:35 ` Re: Question on partitioning Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Broersma Jr @ 2006-11-10 17:39 UTC (permalink / raw)
To: Mark Steben <msteben@autorevenue.com>; pgsql-admin
> 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/interactive/ddl-partitioning.html
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Question on partitioning
2006-11-10 16:29 Question on partitioning Mark Steben <msteben@autorevenue.com>
2006-11-10 17:39 ` Re: Question on partitioning Richard Broersma Jr <rabroersma@yahoo.com>
@ 2006-11-10 18:35 ` Joshua D. Drake <jd@commandprompt.com>
0 siblings, 0 replies; 6+ messages in thread
From: Joshua D. Drake @ 2006-11-10 18:35 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; +Cc: Mark Steben <msteben@autorevenue.com>; pgsql-admin
> > 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?
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Question on partitioning
@ 2006-11-15 20:13 Mark Steben <msteben@autorevenue.com>
2006-11-16 17:38 ` Re: Question on partitioning Simon Riggs <simon@2ndquadrant.com>
0 siblings, 1 reply; 6+ messages in thread
From: Mark Steben @ 2006-11-15 20:13 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Question on partitioning
2006-11-15 20:13 Question on partitioning Mark Steben <msteben@autorevenue.com>
@ 2006-11-16 17:38 ` Simon Riggs <simon@2ndquadrant.com>
2006-11-16 18:13 ` Re: Question on partitioning Mark Steben <msteben@autorevenue.com>
0 siblings, 1 reply; 6+ messages in thread
From: Simon Riggs @ 2006-11-16 17:38 UTC (permalink / raw)
To: Mark Steben <msteben@autorevenue.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Question on partitioning
2006-11-15 20:13 Question on partitioning Mark Steben <msteben@autorevenue.com>
2006-11-16 17:38 ` Re: Question on partitioning Simon Riggs <simon@2ndquadrant.com>
@ 2006-11-16 18:13 ` Mark Steben <msteben@autorevenue.com>
0 siblings, 0 replies; 6+ messages in thread
From: Mark Steben @ 2006-11-16 18:13 UTC (permalink / raw)
To: 'Simon Riggs' <simon@2ndquadrant.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-11-16 18:13 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-11-10 16:29 Question on partitioning Mark Steben <msteben@autorevenue.com>
2006-11-10 17:39 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-11-10 18:35 ` Joshua D. Drake <jd@commandprompt.com>
2006-11-15 20:13 Question on partitioning Mark Steben <msteben@autorevenue.com>
2006-11-16 17:38 ` Simon Riggs <simon@2ndquadrant.com>
2006-11-16 18:13 ` Mark Steben <msteben@autorevenue.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox