X-Original-To: pgsql-performance@postgresql.org Received: from serwer.skawsoft.com.pl (serwer.skawsoft.com.pl [213.25.37.66]) by postgresql.org (Postfix) with ESMTP id 6657147682D for ; Wed, 5 Mar 2003 05:04:28 -0500 (EST) Received: from klaster.net (pi106.krakow.cvx.ppp.tpnet.pl [217.99.209.106]) by serwer.skawsoft.com.pl (Postfix) with ESMTP id EC05E2B88F; Wed, 5 Mar 2003 11:02:30 +0100 (CET) Message-ID: <3E65CBBE.4090803@klaster.net> Date: Wed, 05 Mar 2003 11:04:46 +0100 From: Tomasz Myrta User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; PL; rv:1.1) Gecko/20020826 X-Accept-Language: pl, en-us, en MIME-Version: 1.0 To: Paul McKay Cc: pgsql-performance@postgresql.org Subject: Re: Slow query performance on large table References: <000001c2e2fc$4a13c200$0c64a8c0@paulspc> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Archive-Number: 200303/21 X-Sequence-Number: 1319 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