Re: cannot use multicolumn index

On 14 Září 2011, 15:09, MirrorX wrote:
> here is the explain analyze output->
> server=# explain analyze select count(*) from temp_by_hour where xid > 100
> and xdate > now() - interval '1 week';
> QUERY
> PLAN
> ---------------------------------------------------------------------------------------------------------------------------------------------------
> Aggregate (cost=29359311.58..29359311.59 rows=1 width=0) (actual
> time=2728061.589..2728061.590 rows=1 loops=1)
> -> Seq Scan on temp_by_hour (cost=0.00..29345234.14 rows=5630975
> width=0) (actual time=560446.661..2726838.501 rows=5760724 loops=1)
> Filter: ((xid > 100) AND (xdate > (now() - '7 days'::interval)))
> Total runtime: 2728063.170 ms

Sorry, but with this amount of information, no one can actually help.

- What is the problem, i.e. what behaviour you expect?
- How much data is the table?
- What portion of it matches the conditions?
- What is the index definition?

My bet is the conditions are not selective enough and the index scan would
be less effective than reading the whole table. Try to disable seqscan or
modify the cost variables so that the index scan is used and see if it's
faster or not.

Tomas