Re: Baffling sequential scan plan when index scan would

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(at)interspire(dot)com
web: http://www.interspire.com