From: Jim C. Nasby <jnasby@pervasive.com>
To: Benjamin Krajmalnik <kraj@illumen.com>
Cc: Chris Hoover <revoohc@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Table partitioning
Date: Wed, 26 Apr 2006 17:52:01 -0500
Message-ID: <20060426225201.GH97354@pervasive.com> (raw)
In-Reply-To: <8511B4970E0D124898E973DF496F9B432515CC@stash.stackdump.local>
References: <8511B4970E0D124898E973DF496F9B432515CC@stash.stackdump.local>
Can we see the output of \d tablename as well as EXPLAIN ANALYZE of the
select?
On Wed, Apr 26, 2006 at 02:48:50PM -0600, Benjamin Krajmalnik wrote:
> Actually, right now there is no data in those partitions.
>
> All of the data is currently in the parent table (I have not yet created
> the trigger which will route the data to the correct partition).
>
> I just found to items intriguing - first, that the indices and other
> properties other than the field definition were not inherited (is this
> how this is supposed to work?), and second, that PG first retrieves the
> entire result set and then limits it (or at least that appear to be how
> it is working).
>
> If the order by clause were an expression, I can understand where it
> would have to first retrieve the entire resultset and then limit it.
> However, when we are dealing with an order by clause running on an index
> or primary key, I would figure that it would only retrieve the number of
> rows limited, or if an offset is specified then go to the offset and
> only process the "limit" number of rows.
>
>
>
>
>
> ________________________________
>
> From: Chris Hoover [mailto:revoohc@gmail.com]
> Sent: Wednesday, April 26, 2006 2:33 PM
> To: Benjamin Krajmalnik
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Tale partitioning
>
>
>
> Each of the partition tables needs it's own set of indexes. Build them,
> and see if the does not fix your performance issues. Also, be sure you
> turned on the constraint_exclusion parameter, and each table (other than
> the "master") has an constraint on it that is unique.
>
> HTH,
>
> Chris
>
>
>
>
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: jnasby@pervasive.com, kraj@illumen.com, revoohc@gmail.com
Subject: Re: Table partitioning
In-Reply-To: <20060426225201.GH97354@pervasive.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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