Re: cannot use multicolumn index

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread 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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux