pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Paul McKay <paul_mckay@clearwater-it.co.uk>
To: pgsql-performance@postgresql.org
Subject: Re: Slow query performance on large table
Date: Wed, 5 Mar 2003 09:47:51 -0000
Message-ID: <000001c2e2fc$4a13c200$0c64a8c0@paulspc> (raw)
In-Reply-To: <3E64E079.8050807@klaster.net>


Hopefully you guys can help me with another query I've got that's
running slow.

This time it's across two tables I have

clearview=# \d panconversation
      Table "panconversation"
   Column    |  Type   | Modifiers
-------------+---------+-----------
 assessment  | integer | not null
 interface   | integer |
 source      | integer |
 destination | integer |
 protocol    | integer |
Indexes: idx_panconversation_destination,
         idx_panconversation_interface,
         idx_panconversation_protocol,
         idx_panconversation_source
Primary key: panconversation_pkey
Unique keys: unq_panconversation
Triggers: RI_ConstraintTrigger_52186648,
          RI_ConstraintTrigger_52186654,
          RI_ConstraintTrigger_52186660,
          RI_ConstraintTrigger_52186666

Primary key is assessment

Along with the table I was dealing with before, with the index I'd
mislaid put back in

clearview=# \d measurement
              Table "measurement"
   Column   |         Type          | Modifiers
------------+-----------------------+-----------
 assessment | integer               |
 time       | integer               |
 value      | character varying(50) |
Indexes: idx_measurement_assessment,
         idx_measurement_time,
         ind_measurement_ass_time

The 'explain analyse' of the query I am running is rather evil.

clearview=# explain analyse select source,value
clearview-#            from measurement, PANConversation
clearview-#            where PANConversation.assessment =
measurement.assessment
clearview-#            and Interface = 11
clearview-#            and Time > 1046184261 and Time < 1046335461
clearview-# ;
NOTICE:  QUERY PLAN:

Hash Join  (cost=1532.83..345460.73 rows=75115 width=23) (actual
time=1769.84..66687.11 rows=16094 loops=1)
  ->  Seq Scan on measurement  (cost=0.00..336706.07 rows=418859
width=15) (actual time=1280.11..59985.47 rows=455788 loops=1)
  ->  Hash  (cost=1498.21..1498.21 rows=13848 width=8) (actual
time=253.49..253.49 rows=0 loops=1)
        ->  Seq Scan on panconversation  (cost=0.00..1498.21 rows=13848
width=8) (actual time=15.64..223.18 rows=13475 loops=1)
Total runtime: 66694.82 msec

EXPLAIN

Anybody shed any light on why the indexes I created aren't being used,
and I have these nasty sequential scans?

Thanks in advance,

Paul.
======================================
Paul Mckay
Consultant Partner
Servicing Division
Clearwater-IT
e:paul_mckay@clearwater-it.co.uk
t:0161 877 6090
m: 07713 510946
======================================

-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Tomasz
Myrta
Sent: 04 March 2003 17:21
To: Andreas Pflug
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Slow query performance on large table

Andreas Pflug wrote:

> Tom,
> 
> does this mean that a primary key alone might not be enough? As far as
I 
> understood Paul, the PK looks quite as the newly created index does,
so 
> "create index ind_meas on measurement (assessment,time)"  should
perform 
> the same as "... primary key(assessment,time)".
> Do possibly non-optimal indices (only assessment, only time as Paul 
> described earlier) screw up the optimizer, igoring the better option 
> usiing the PK? Obviously, the index used should be combined of 
> (assessment,time) but IMHO a PK should be enough.
> 
> regards,
> 
> Andreas
You are right - primary key should be ok, but Paul lost it. psql \d 
shows primary key indexes, but in this case there was no such primary
key.

Regards,
Tomasz Myrta


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org




view thread (18+ messages)  latest in thread

Message-ID: <000001c2e2fc$4a13c200$0c64a8c0@paulspc>
Permalink:  ../000001c2e2fc$4a13c200$0c64a8c0@paulspc/
Also on:    postgresql.org/message-id/000001c2e2fc$4a13c200$0c64a8c0@paulspc

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: paul_mckay@clearwater-it.co.uk
  Subject: Re: Slow query performance on large table
  In-Reply-To: <000001c2e2fc$4a13c200$0c64a8c0@paulspc>
  References: <3E64E079.8050807@klaster.net> <000001c2e2fc$4a13c200$0c64a8c0@paulspc>

* 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