Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 4664DB5DC13 for ; Wed, 12 Oct 2011 01:39:17 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 55878-02 for ; Wed, 12 Oct 2011 04:39:09 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from toroondcbmts04-srv.bellnexxia.net (toroondcbmts04-srv.bellnexxia.net [207.236.237.38]) by mail.postgresql.org (Postfix) with ESMTP id 23855B5DC01 for ; Wed, 12 Oct 2011 01:39:08 -0300 (ADT) Received: from toip54-bus.srvr.bell.ca ([67.69.240.140]) by toroondcbmts04-srv.bellnexxia.net (InterMail vM.8.00.01.00 201-2244-105-20090324) with ESMTP id <20111012043907.BKYY3212.toroondcbmts04-srv.bellnexxia.net@toip54-bus.srvr.bell.ca>; Wed, 12 Oct 2011 00:39:07 -0400 Received: from toip39-bus.srvr.bell.ca ([67.69.240.40]) by toip54-bus.srvr.bell.ca with ESMTP; 12 Oct 2011 00:39:07 -0400 X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: Ak8FAAoYlU5GMVId/2dsb2JhbABDgk2aQosegQWBUwEBBQglQQsNAwIGAw0BAwQBAQEJBBoHGQgSEwkIAQEEARIIAQKHerg9hndhBJk8hGSHRA Received: from unknown (HELO CAPRICA) ([70.49.82.29]) by toip39-bus.srvr.bell.ca with ESMTP; 12 Oct 2011 00:39:06 -0400 From: "Carlo Stonebanks" To: "'Dave Crooke'" , "'Claudio Freire'" Cc: References: In-Reply-To: Subject: Re: Composite keys Date: Wed, 12 Oct 2011 00:39:08 -0400 Message-ID: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_058A_01CC8877.5A522380" X-Mailer: Microsoft Office Outlook 11 Thread-Index: AcyIfmzUeV4cD6bmQBuAO0pf9PLEugAEjiag X-MimeOLE: Produced By Microsoft MimeOLE V6.1.7601.17609 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201110/127 X-Sequence-Number: 45349 This is a multi-part message in MIME format. ------=_NextPart_000_058A_01CC8877.5A522380 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Thanks Dave & Claudio. Unfortunately, my specific example had a primary key in it (based on a real-world case) but this kind of distracted from the general point. So with PG I will stick to the general SQL rule that IF I use compound keys then we have the most selective columns to the left. correct? _____ From: Dave Crooke [mailto:dcrooke@gmail.com] Sent: October 11, 2011 9:28 PM To: Claudio Freire Cc: Carlo Stonebanks; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Composite keys Claudio is on point, I'll be even more pointed .... If pkey_id truly is a primary key in the database sense of the term, and thus unique, then IIUC there is no circumstance in which your composite index would ever even get used ... all it's doing is slowing down writes :-) If the query is sufficiently selective on pkey_id to merit using an index, then the planner will use the primary key index, because it's narrower; if not, then the only other option is to do a full table scan because there is no index of which another_id is a prefix. There are only three options which make sense: 1. No additional indexes, just the primary key 2. An additional index on (another_id) 3. An additional index on (another_id, pkey_id) 4. Both 2. and 3. Choosing between these depends on a lot of variables of the query mix in practice ... you could set up both 2. and 3. and then see which indexes the planner actually uses in practice and then decide which to keep. The value in having pkey_id in the index in 3. is for queries whose primary selectivity is on another_id, but which also have some selectivity on pkey_id .... the planner can use an index scan to filter candidate rows / blocks to look at. This is especially helpful if another_id is not very selective and / or the rows are quite wide. On gut feel, it seems unlikely that you'd have a real-world circumstance in which it makes sense to choose option 4. but it can't be ruled out without further context. Cheers Dave On Tue, Oct 11, 2011 at 7:52 PM, Claudio Freire wrote: On Tue, Oct 11, 2011 at 5:16 PM, Carlo Stonebanks wrote: > Question 2) Regardless of the answer to Question 1 - if another_id is not > guaranteed to be unique, whereas pkey_id is - there any value to changing > the order of declaration (more generally, is there a performance impact for > column ordering in btree composite keys?) Multicolumn indices on (c1, c2, ..., cn) can only be used on where clauses involving c1..ck with k

Thanks Dave & Claudio. =

 

Unfortunately, my specific example = had a primary key in it (based on a real-world case) but this kind of = distracted from the general point.

 

So with PG I will stick to the = general SQL rule that IF I use compound keys then we have the most selective columns = to the left… correct?

 

 

 

 

From: Dave Crooke [mailto:dcrooke@gmail.com]
Sent: October 11, 2011 = 9:28 PM
To: Claudio Freire
Cc: Carlo Stonebanks; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] = Composite keys

 

Claudio is on = point, I'll be even more pointed ....

If pkey_id truly is a primary key in the database sense of the term, and = thus unique, then IIUC there is no circumstance in which your composite index = would ever even get used ... all it's doing is slowing down writes :-) If the = query is sufficiently selective on pkey_id to merit using an index, then the = planner will use the primary key index, because it's narrower; if not, then the = only other option is to do a full table scan because there is no index of = which another_id is a prefix.

There are only three options which make sense:

1. No additional indexes, just the primary key
2. An additional index on (another_id)
3. An additional index on (another_id, pkey_id)
4. Both 2. and 3.

Choosing between these depends on a lot of variables of the query mix in practice ... you could set up both 2. and 3. and then see which indexes = the planner actually uses in practice and then decide which to keep.

The value in having pkey_id in the index in 3. is for queries whose = primary selectivity is on another_id, but which also have some selectivity on = pkey_id .... the planner can use an index scan to filter candidate rows / blocks = to look at. This is especially helpful if another_id is not very selective = and / or the rows are quite wide.

On gut feel, it seems unlikely that you'd have a real-world circumstance = in which it makes sense to choose option 4. but it can't be ruled out = without further context.

Cheers
Dave

On Tue, Oct 11, 2011 at 7:52 PM, Claudio Freire <klaussfreire@gmail.com> = wrote:

On Tue, Oct = 11, 2011 at 5:16 PM, Carlo Stonebanks
<stonec.register@sympatico.ca= > wrote:
> Question 2) Regardless of the answer to Question 1 - if another_id = is not
> guaranteed to be unique, whereas pkey_id is – there any value = to changing
> the order of declaration (more generally, is there a performance = impact for
> column ordering in btree composite = keys?)

Multicolumn indices on (c1, c2, ..., cn) can only be used on = where
clauses involving c1..ck with k<n.

So, an index on (a,b) does *not* help for querying on b.

Furthermore, if a is unique, querying on a or querying on a and b is
equally selective. b there is just consuming space and cpu cycles.

I'd say, although it obviously depends on the queries you issue, you
only need an index on another_id.

--
Sent via pgsql-performance mailing list (pgsql-performance@postgr= esql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

 

------=_NextPart_000_058A_01CC8877.5A522380--