pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jord Tanner <jord@indygecko.com>
To: Fabian Kreitner <fabian.kreitner@ainea-ag.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: index / sequential scan problem
Date: 17 Jul 2003 07:10:35 -0700
Message-ID: <1058451034.2010.4.camel@gecko> (raw)
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>


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 <jord@indygecko.com>




view thread (19+ messages)  latest in thread

Message-ID: <1058451034.2010.4.camel@gecko>
Permalink:  ../1058451034.2010.4.camel@gecko/
Also on:    postgresql.org/message-id/1058451034.2010.4.camel@gecko

 · 

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: jord@indygecko.com, fabian.kreitner@ainea-ag.de
  Subject: Re: index / sequential scan problem
  In-Reply-To: <1058451034.2010.4.camel@gecko>
  References: <5.1.0.14.0.20030717131157.03957fa0@195.145.148.245> <5.1.0.14.0.20030717105203.03d2b5c0@195.145.148.245> <20030717111248.C27264@bacon> <5.1.0.14.0.20030717144625.039c4a10@195.145.148.245> <1058451034.2010.4.camel@gecko>

* 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