Optimizer(?) off by factor of 3 ... ?

From: "Marc G(dot) Fournier" <scrappy(at)hub(dot)org>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Optimizer(?) off by factor of 3 ... ?
Date: 2002-02-11 14:23:52
Message-ID: 20020211102132.K5231-100000@mail1.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


Or is this in the planner? Same query, same tables, one with seqscan
enabled, one with it disabled (btw, whomever added the ANALYZE to EXPLAIN,
pure genius):

iwantu=# explain analyze SELECT poc.uid,headline,pictures,voice FROM orient poc JOIN clubs c ON (poc.uid = c.uid AND c.club = 3 );
NOTICE: QUERY PLAN:

Nested Loop (cost=0.00..1791417.52 rows=26566 width=72) (actual time=0.55..3345.13 rows=23510 loops=1)
-> Index Scan using clubs_idx on clubs c (cost=0.00..1695474.62 rows=26569 width=64) (actual time=0.48..1936.95 rows=23510 loops=1)
-> Index Scan using orient_pkey on orient poc (cost=0.00..3.60 rows=1 width=8) (actual time=0.03..0.03 rows=1 loops=23510)
Total runtime: 3474.93 msec

iwantu=# set enable_seqscan=true;
iwantu=# explain analyze SELECT poc.uid,headline,pictures,voice FROM orient poc JOIN clubs c ON (poc.uid = c.uid AND c.club = 3 );
NOTICE: QUERY PLAN:

Hash Join (cost=31693.56..47033.86 rows=26566 width=72) (actual time=1044.41..11450.85 rows=23510 loops=1)
-> Seq Scan on orient poc (cost=0.00..7718.69 rows=485969 width=8) (actual time=0.01..3484.00 rows=485969 loops=1)
-> Hash (cost=31627.14..31627.14 rows=26569 width=64) (actual time=1034.14..1034.14 rows=0 loops=1)
-> Seq Scan on clubs c (cost=0.00..31627.14 rows=26569 width=64) (actual time=593.80..836.72 rows=23510 loops=1)
Total runtime: 11583.36 msec

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Marc G. Fournier 2002-02-11 14:40:16 Yeech ... more on SEQSCAN vs having it disabled ...
Previous Message SAKAIDA 2002-02-11 12:10:36 pgbash-2.4 released