From: Tomasz Myrta <jasiek@klaster.net>
To: Paul McKay <paul_mckay@clearwater-it.co.uk>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow query performance on large table
Date: Wed, 05 Mar 2003 11:04:46 +0100
Message-ID: <3E65CBBE.4090803@klaster.net> (raw)
References: <000001c2e2fc$4a13c200$0c64a8c0@paulspc>
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
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: jasiek@klaster.net, paul_mckay@clearwater-it.co.uk
Subject: Re: Slow query performance on large table
In-Reply-To: <3E65CBBE.4090803@klaster.net>
References: <000001c2e2fc$4a13c200$0c64a8c0@paulspc> <3E65CBBE.4090803@klaster.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