Search Postgresql Archives

Re: Baffling sequential scan plan when index scan would

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux