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: 'Tomasz Myrta' <jasiek@klaster.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow query performance on large table
Date: Wed, 5 Mar 2003 10:27:27 -0000
Message-ID: <000201c2e301$d1cc3a10$0c64a8c0@paulspc> (raw)
In-Reply-To: <3E65CBBE.4090803@klaster.net>



clearview=# select count(*) from measurement;
  count
----------
 15302138
(1 row)

clearview=# select count(*) from panconversation;
 count
-------
 77217
(1 row)

clearview=# select count(*) from panconversation where interface = 11;
 count
-------
 13475
(1 row)

clearview=# select count(*) from measurement where time > 1046184261 and
time < 1046335461;
 count
--------
 455788
(1 row)

======================================
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: 05 March 2003 10:05
To: Paul McKay
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Slow query performance on large table

Paul McKay wrote:
> 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?

Measurement is sequentially scaned, because probably "interface=12" 
results in lot of records.

Please, check how many rows you have
- all rows in measurement/panconversation,
- rows in measurement with "Interface"=12
- rows in panconversation between your time.

Regards,
Tomasz Myrta



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster




view thread (18+ messages)  latest in thread

Message-ID: <000201c2e301$d1cc3a10$0c64a8c0@paulspc>
Permalink:  ../000201c2e301$d1cc3a10$0c64a8c0@paulspc/
Also on:    postgresql.org/message-id/000201c2e301$d1cc3a10$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, jasiek@klaster.net
  Subject: Re: Slow query performance on large table
  In-Reply-To: <000201c2e301$d1cc3a10$0c64a8c0@paulspc>
  References: <3E65CBBE.4090803@klaster.net> <000201c2e301$d1cc3a10$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