Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] Optimizer badness in 7.0 beta

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: bhirt(at)mobygames(dot)com
Cc: pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: [HACKERS] Optimizer badness in 7.0 beta
Date: 2000-03-08 00:15:10
Message-ID: 22796.952474510@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Brian Hirt <bhirt(at)mobygames(dot)com> writes:
> -- PG 7.0 --
> NOTICE:  QUERY PLAN:

> Sort  (cost=383940.72..383940.72 rows=905 width=59)
>   ->  Seq Scan on game  (cost=0.00..383896.28 rows=905 width=59)
>         SubPlan
>           ->  Unique  (cost=0.00..808.88 rows=0 width=4)
>                 ->  Index Scan using game_developer_game_index on game_developer  (cost=0.00..808.87 rows=4 width=4)

There's something very strange about this query plan --- why is the
estimated cost of the indexscan so high?  If I do, say,

regression=# explain select distinct * from tenk1 where unique1 < 3;
NOTICE:  QUERY PLAN:

Unique  (cost=3.22..3.34 rows=0 width=148)
  ->  Sort  (cost=3.22..3.22 rows=3 width=148)
        ->  Index Scan using tenk1_unique1 on tenk1  (cost=0.00..3.19 rows=3 width=148)

The tenk1 table from the regression database is only 10K rows, versus
15K in your table, but still I'd expect costs not a heck of a lot higher
than one page fetch per tuple retrieved.  How is it coming up with a
cost of 800 to retrieve 4 tuples?

Could I trouble you for the exact declarations of the tables and indices
involved here?  Also, what plan do you get from 7.0 if you do

	set enable_indexscan = 'off';

before the EXPLAIN?

			regards, tom lane

In response to

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2000-03-08 00:17:33
Subject: Re: [HACKERS] DROP TABLE inside a transaction block
Previous:From: Bruce MomjianDate: 2000-03-08 00:13:06
Subject: Re: [HACKERS] xlog.c.patch for cygwin port.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group