From: Gregory Wood <gregw@com-stock.com>
To: Dmitry Tkach <dmitry@openratings.com>
Cc: PostgreSQL-General <pgsql-general@postgresql.org>
Subject: Re: Why is it not using an index?
Date: Fri, 15 Mar 2002 14:20:11 -0500
Message-ID: <001401c1cc56$6db77d40$7889ffcc@comstock.com> (raw)
References: <3C924680.4090806@openratings.com>
<002801c1cc4f$25dba980$7889ffcc@comstock.com>
<3C92530D.2050005@openratings.com>
> Am I missing something here again, or will it just not use an index for
aggregation?
PostgreSQL does not use an index to perform a full table count. I'm not sure
of the exact reasoning behind this, but I think there are multiple issues
with the approach.
> I mean, especially an this case, it looks so weird that it KNOWS the
answer to my query RIGHT AWAY (rows=... in the explain response), yet it
takes it so long to return it...
Actually, that rows= count is the *estimate* for the number of rows. That
estimate is calculated from a variety of statistics compiled when the
ANALYZE command is performed. Those statistics may or may not be up to date,
and are only used to plan the query's execution.
Greg
----- Original Message -----
From: Dmitry Tkach
To: Gregory Wood
Cc: PostgreSQL-General
Sent: Friday, March 15, 2002 3:01 PM
Subject: Re: [GENERAL] Why is it not using an index?
Gregory Wood wrote:
explain select * from a where x=3;
PostgreSQL is treating 3 as an int4 (integer) type, whereas x is an
int2(smallint) type. Try casting the constant as a smallint and it should
usethe index:explain select * from a where x=3::smallint;
Aha! Great! Thanks a lot! That worked!
Now, the next problem:
explain select count (x) from a ;
Aggregate (cost=100175934.05..100175934.05 rows=1 width=2)
-> Seq Scan on a (cost=100000000.00..100150659.04 rows=10110004 width=2)
Am I missing something here again, or will it just not use an index for
aggregation?
I mean, especially an this case, it looks so weird that it KNOWS the answer
to my query RIGHT AWAY (rows=... in the explain response), yet it takes it
so long to return it...
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-sql@postgresql.org
Cc: gregw@com-stock.com, dmitry@openratings.com, pgsql-general@postgresql.org
Subject: Re: Why is it not using an index?
In-Reply-To: <001401c1cc56$6db77d40$7889ffcc@comstock.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