X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.225.181]) by svr1.postgresql.org (Postfix) with ESMTP id E3488CC9E3 for ; Thu, 17 Jul 2003 14:10:34 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr5.postgresql.org [64.117.225.181]) (amavisd-new, port 10024) with ESMTP id 38345-01 for ; Thu, 17 Jul 2003 14:10:48 +0000 (GMT) Received: from indygecko.com (h24-77-222-182.ok.shawcable.net [24.77.222.182]) by svr1.postgresql.org (Postfix) with ESMTP id 3823CCC9FA for ; Thu, 17 Jul 2003 11:10:22 -0300 (ADT) Received: from [192.168.10.10] ([::ffff:192.168.10.10]) by indygecko.com with esmtp; Thu, 17 Jul 2003 07:10:39 -0700 Subject: Re: index / sequential scan problem From: Jord Tanner To: Fabian Kreitner Cc: pgsql-performance@postgresql.org In-Reply-To: <5.1.0.14.0.20030717144625.039c4a10@195.145.148.245> References: <5.1.0.14.0.20030717131157.03957fa0@195.145.148.245> <5.1.0.14.0.20030717105203.03d2b5c0@195.145.148.245> <5.1.0.14.0.20030717105203.03d2b5c0@195.145.148.245> <20030717111248.C27264@bacon> <5.1.0.14.0.20030717131157.03957fa0@195.145.148.245> <5.1.0.14.0.20030717144625.039c4a10@195.145.148.245> Organization: Message-Id: <1058451034.2010.4.camel@gecko> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.2.2 (1.2.2-5) Date: 17 Jul 2003 07:10:35 -0700 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/253 X-Sequence-Number: 2546 I've seen similar behavior in my own queries. I found that reducing random_page_cost from the default of 4 down to 2 caused the query to choose the index, and resulted in an order of magnitude improvement on some queries. On Thu, 2003-07-17 at 05:50, Fabian Kreitner wrote: > At 14:34 17.07.2003, you wrote: > > >On 17/07/2003 12:13 Fabian Kreitner wrote: > >>That is what I read too and is why Im confused that the index is indeed > >>executing faster. Can this be a problem with the hardware and/or > >>postgress installation? > > > > > >It's more likely that the OS has most of the data cached after the first > >query and so doesn't need to re-read that data from disk when you retry > >the query with seq scan disabled. Try something like this: > > > >set enable_seqscan to true; > >explain analyze ...... > >set enable_seqscan to false; > >explain analyze ...... > >set enable_seqscan to true; > >explain analyze ...... > > > >I expect you will find that the third query is also a lot faster that the > >first query. > > Im afraid, no. > Database has been stopped / started right before this. > > perg_1097=# set enable_seqscan to true; > SET VARIABLE > perg_1097=# EXPLAIN ANALYZE select notiz_id, obj_id, obj_typ > perg_1097-# from notiz_objekt a > perg_1097-# where not exists > perg_1097-# ( > perg_1097(# select 1 > perg_1097(# from notiz_gelesen b > perg_1097(# where ma_id = 2001 > perg_1097(# and ma_pid = 1097 > perg_1097(# and a.notiz_id = b.notiz_id > perg_1097(# ) > perg_1097-# ; > NOTICE: QUERY PLAN: > > Seq Scan on notiz_objekt a (cost=0.00..56125.80 rows=15561 width=12) > (actual time=0.28..2298.71 rows=31122 loops=1) > SubPlan > -> Seq Scan on notiz_gelesen b (cost=0.00..1.79 rows=1 width=0) > (actual time=0.07..0.07 rows=0 loops=31122) > Total runtime: 2327.37 msec > > EXPLAIN > perg_1097=# set enable_seqscan to false; > SET VARIABLE > perg_1097=# EXPLAIN ANALYZE select notiz_id, obj_id, obj_typ > perg_1097-# from notiz_objekt a > perg_1097-# where not exists > perg_1097-# ( > perg_1097(# select 1 > perg_1097(# from notiz_gelesen b > perg_1097(# where ma_id = 2001 > perg_1097(# and ma_pid = 1097 > perg_1097(# and a.notiz_id = b.notiz_id > perg_1097(# ) > perg_1097-# ; > NOTICE: QUERY PLAN: > > Seq Scan on notiz_objekt a (cost=100000000.00..100111719.36 rows=15561 > width=12) (actual time=0.25..535.75 rows=31122 loops=1) > SubPlan > -> Index Scan using idx_notiz_gelesen_2 on notiz_gelesen > b (cost=0.00..3.57 rows=1 width=0) (actual time=0.01..0.01 rows=0 loops=31122) > Total runtime: 567.94 msec > > EXPLAIN > perg_1097=# set enable_seqscan to true; > SET VARIABLE > perg_1097=# EXPLAIN ANALYZE select notiz_id, obj_id, obj_typ > perg_1097-# from notiz_objekt a > perg_1097-# where not exists > perg_1097-# ( > perg_1097(# select 1 > perg_1097(# from notiz_gelesen b > perg_1097(# where ma_id = 2001 > perg_1097(# and ma_pid = 1097 > perg_1097(# and a.notiz_id = b.notiz_id > perg_1097(# ) > perg_1097-# ; > NOTICE: QUERY PLAN: > > Seq Scan on notiz_objekt a (cost=0.00..56125.80 rows=15561 width=12) > (actual time=0.13..2300.74 rows=31122 loops=1) > SubPlan > -> Seq Scan on notiz_gelesen b (cost=0.00..1.79 rows=1 width=0) > (actual time=0.07..0.07 rows=0 loops=31122) > Total runtime: 2330.25 msec > > EXPLAIN > perg_1097=# > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Jord Tanner