From: Tomas Vondra <tv@fuzzy.cz>
To: MirrorX <mirrorx@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: cannot use multicolumn index
Date: Wed, 14 Sep 2011 16:14:55 +0200
Message-ID: <0ac4fa43876e63ec84515656f233960f.squirrel@sq.gransy.com> (raw)
In-Reply-To: <1316005753084-4802699.post@n5.nabble.com>
References: <1316004607207-4802634.post@n5.nabble.com>
<1316005753084-4802699.post@n5.nabble.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tv@fuzzy.cz, mirrorx@gmail.com
Subject: Re: cannot use multicolumn index
In-Reply-To: <0ac4fa43876e63ec84515656f233960f.squirrel@sq.gransy.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox