Received: from exchange.hq.openratings.com (adsl-141-154-210-2.ba-dsg.net [141.154.210.2]) by postgresql.org (Postfix) with ESMTP id 6B78A4769A4 for ; Fri, 15 Mar 2002 13:32:16 -0500 (EST) Received: from openratings.com (cancun.hq.openratings.com [10.0.0.225]) by exchange.hq.openratings.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id GPKPL5H1; Fri, 15 Mar 2002 13:32:22 -0500 Message-ID: <3C92530D.2050005@openratings.com> Date: Fri, 15 Mar 2002 15:01:17 -0500 From: Dmitry Tkach User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:0.9.7+) Gecko/20020123 X-Accept-Language: en-us MIME-Version: 1.0 To: Gregory Wood Cc: PostgreSQL-General Subject: Re: Why is it not using an index? References: <3C924680.4090806@openratings.com> <002801c1cc4f$25dba980$7889ffcc@comstock.com> Content-Type: text/html; charset=KOI8-R Content-Transfer-Encoding: quoted-printable X-Archive-Number: 200203/838 X-Sequence-Number: 22210 Gregory Wood wrote:
explain select * from a where x=3D3;

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 use
the index:

explain select * from a = where x=3D3::smallint;

Aha! =9AGreat! Thanks a lot! That worked!
Now, the next problem:

explain select count (x) from a ;

Aggregate=9A (cost=3D100175934.05..100175934.05 rows=3D1 width=3D2)
=9A ->=9A Seq Scan on a=9A (cost=3D100000000.00..100150659.04 rows=3D101= 10004 width=3D2)

Am I missing something here again, or will it just not use an index for agg= regation?

I mean, especially an this case, it looks so weird that it KNOWS the answer to my query RIGHT AWAY (rows=3D... in the explain response), yet it takes it so long to return it...