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@xxxxxxxxxxxxxx web: http://www.interspire.com
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx
