Received: from mail.com-stock.com (mail.com-stock.com [204.255.137.254]) by postgresql.org (Postfix) with ESMTP id 5635C475469 for ; Fri, 15 Mar 2002 14:14:48 -0500 (EST) Received: by mail.com-stock.com (Postfix, from userid 1000) id 454C167; Fri, 15 Mar 2002 14:14:03 -0500 (EST) Received: from deimos (deimos.com-stock.com [204.255.137.120]) by mail.com-stock.com (Postfix) with SMTP id C0D0AD9FD; Fri, 15 Mar 2002 14:14:00 -0500 (EST) Message-ID: <001401c1cc56$6db77d40$7889ffcc@comstock.com> From: "Gregory Wood" To: "Dmitry Tkach" Cc: "PostgreSQL-General" References: <3C924680.4090806@openratings.com> <002801c1cc4f$25dba980$7889ffcc@comstock.com> <3C92530D.2050005@openratings.com> Subject: Re: Why is it not using an index? Date: Fri, 15 Mar 2002 14:20:11 -0500 Organization: Comstock Net Services MIME-Version: 1.0 Content-Type: text/plain; charset="KOI8-R" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4807.1700 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4807.1700 X-Archive-Number: 200203/840 X-Sequence-Number: 22212 > 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...