This thread make me to think about the question:
could this "feature" be used in select count(*) type
query that force it to use index?
I make a little test, but found a strange phenoment,
created a simple table:
create table partial_idx_t(id serial, f1 integer, f2 text);
then insert many rows into it. then build a partial index
on it:
create index partial_idx on partial_idx_t (id) where id >=1 and id <=
2000000000;
my purpose is to see if I could use partial index while doing count(*),
it seems the index being used after a 'set enable_seqscan=off':
laser=# explain analyze select count(*) from partial_idx_t where id >=1
and id <=2000000000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=13933.39..13933.39 rows=1 width=0) (actual
time=1901.761..1901.762 rows=1 loops=1)
-> Index Scan using partial_idx on partial_idx_t (cost=0.00..12622.93
rows=524183 width=0) (actual time=0.130..1230.634 rows=524288 loops=1)
Index Cond: ((id >= 1) AND (id <= 2000000000))
Total runtime: 1901.876 ms
but it seems a count(*) without WHERE condition is still faster:
laser=# explain analyze select count(*) from partial_idx_t;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=100009638.60..100009638.60 rows=1 width=0) (actual
time=1567.317..1567.318 rows=1 loops=1)
-> Seq Scan on partial_idx_t (cost=100000000.00..100008327.88
rows=524288 width=0) (actual time=0.046..906.747 rows=524288 loops=1)
Total runtime: 1567.401 ms
but the cost field of the explain result that used partial index is really
lower. but the runtime been much more high (I'm with default planner
setting).
How can I understand above? (BTW, all test done after a vacuum full
analyze).
regards laser
