Re: Baffling sequential scan plan when index scan would - Mailing list pgsql-general

Hi Jeff,

Maybe because the key is a bigint - you're looking for an int (int4).

Try casting it to a bigint ?

Jeffrey W. Baker wrote:
> I always thought I would not be the kind of person who writes to this
> list asking why the planner is using a sequential scan.  I always looked
> upon such people as newcomers who would eventually learn the mysterious
> wonders of the Pg query execution planner.
>
> But really, this plan is bizarre!  Why is it scanning sequentially for
> ONE tuple as selected by the primary key?  I even increased stats to
> 1000 and disable seq_scan, but it still insists it cannot do an index
> scan.
>
> skunk=# \d items;
>       Table "items"
>    Column   |   Type   | Modifiers
> ------------+----------+-----------
>  item       | bigint   | not null
> [...]
> Indexes:
>     "items_pkey" primary key, btree (item)
>
> skunk=# analyze verbose items;
> INFO:  analyzing "items"
> INFO:  "items": 80372 pages, 300000 rows sampled, 2660996 estimated total rows
> ANALYZE
>
> skunk=# explain analyze select * from items where item = 2143888;
>                                                       QUERY PLAN
 
>
-----------------------------------------------------------------------------------------------------------------------
>  Seq Scan on items  (cost=100000000.00..100113634.45 rows=1 width=115) (actual time=4034.564..8859.082 rows=1
loops=1)
>    Filter: (item = 2143888)
>  Total runtime: 8859.160 ms
> (3 rows)
>
>  enable_hashagg                 | on
>  enable_hashjoin                | on
>  enable_indexscan               | on
>  enable_mergejoin               | on
>  enable_nestloop                | on
>  enable_seqscan                 | off <===
>  enable_sort                    | on
>  enable_tidscan                 | on
>
>  random_page_cost               | 1
>  cpu_index_tuple_cost           | 0.001
>  cpu_operator_cost              | 0.0025
>  cpu_tuple_cost                 | 0.01
>
> What's even more baffling is the planner will use index scan for any
> other indexed column, including columns for which the index is not
> particularly selective, like item category or date:
>
> skunk=# set enable_seqscan=on;
> SET
>
> skunk=# explain select * from items where category = 245;
>                                       QUERY PLAN
> --------------------------------------------------------------------------------------
>  Index Scan using items_cat_idx on items  (cost=0.00..69887.77 rows=125795 width=115)
>    Index Cond: (category = 245)
> (2 rows)
>
> skunk=# explain select * from items where startdate = '2005-03-01';
>                                       QUERY PLAN
> --------------------------------------------------------------------------------------
>  Index Scan using items_start_ids on items  (cost=0.00..1948.53 rows=30283 width=115)
>    Index Cond: (startdate = '2005-03-01'::date)
> (2 rows)
>
> So it seems that an index scan returning a half-million tuples is OK,
> but an index scan returning a single tuple is right out.  What?
>
> -Confused in California
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

--
Regards,

Chris Smith

  Unit 2, 3 National Street, Rozelle, NSW 2039 Australia

Ph: +61 2 9555 5570
Fx: +61 2 9555 5571

email: info@interspire.com
web: http://www.interspire.com