pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Chris Trawick <ctrawick@cultured.net>
To: pgsql-performance@postgresql.org
Subject: "fun with multipart primary keys" hobby kit
Date: Sat, 03 Jan 2004 00:37:43 -0500
Message-ID: <1073108262.733.27.camel@unity.basin.cultured.net> (raw)

I tried searching the archives to find something like this.  The search
function doesn't like me much, and believe me the feeling is mutual.  So
I'm forced to pollute your inboxes with yet another "why the hell isn't
this thing using my index" email.  I apologize in advance.

I have a many-to-many relation table with a multipart primary key:

siren=# \d listcontact
        Table "public.listcontact"
     Column     |  Type   | Modifiers 
----------------+---------+-----------
 contactlistid  | integer | not null
 contactid      | bigint  | not null
Indexes: listcontact_pkey primary key btree (contactlistid, contactid)

(There were some FKs in there too, but I stripped off everything I could
during my investigation and they didn't survive.)  I'm doing some
performance testing so I loaded it with a few elephant piles:

siren=# select count(*) from listcontact;
  count  
---------
 1409196
(1 row)

And packed it down good:

siren=# vacuum full analyze;
VACUUM

I didn't get the performance I expected.  I took one of our queries and
mutilated it and found some curious behavior on this table.  I started
running queries on just this table and couldn't explain what I was
seeing.  I tried this:

siren=# EXPLAIN ANALYZE SELECT * FROM ListContact WHERE contactListID=-1
AND contactID=91347;

                                               QUERY
PLAN                                                
---------------------------------------------------------------------------------------------------------
 Seq Scan on listcontact  (cost=0.00..29427.94 rows=1 width=12) (actual
time=893.15..5079.52 rows=1 loops=1)
   Filter: ((contactlistid = -1) AND (contactid = 91347))
 Total runtime: 5079.74 msec
(3 rows)

A seqscan...  Fair enough, there's lots of memory on this box.  I didn't
want to see a seqscan though, I wanted to see an index.  So, I disabled
seqscan and tried it again:

                                                          QUERY
PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Index Scan using listcontact_pkey on listcontact  (cost=0.00..58522.64
rows=1 width=12) (actual time=402.73..9419.77 rows=1 loops=1)
   Index Cond: (contactlistid = -1)
   Filter: (contactid = 91347)
 Total runtime: 9419.97 msec
(4 rows)

Am I reading this right?  Is it only using half of the fully-qualified
pk index?  How do I diagnose this?  Has anyone seen this before?

postgresql 7.3.1
linux 2.6.0
quad xeon 450

chris




view thread (3+ messages)  latest in thread

Message-ID: <1073108262.733.27.camel@unity.basin.cultured.net>
Permalink:  ../1073108262.733.27.camel@unity.basin.cultured.net/
Also on:    postgresql.org/message-id/1073108262.733.27.camel@unity.basin.cultured.net

 · 

reply

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-performance@postgresql.org
  Cc: ctrawick@cultured.net
  Subject: Re: "fun with multipart primary keys" hobby kit
  In-Reply-To: <1073108262.733.27.camel@unity.basin.cultured.net>

* 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