Re: Strange logic for partial index proving

laser wrote:
> 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?

No. Because of issues with concurrent updates to the table. See archives
for discussion.

> 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

OK - the time is less for the seq-scan because with an index it has to
go to the index, find a row, go to the table and check that row is
visible. With the seq-scan it just works through the table.

> 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).

I'd say your configuration settings are a long way from accurate.
Probably the general/performance lists would be a better place to
discuss this though.

-- Richard Huxton Archonet Ltd