pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dmitry Tkach <dmitry@openratings.com>
To: Gregory Wood <gregw@com-stock.com>
Cc: PostgreSQL-General <pgsql-general@postgresql.org>
Subject: Re: Why is it not using an index?
Date: Fri, 15 Mar 2002 15:01:17 -0500
Message-ID: <3C92530D.2050005@openratings.com> (raw)
References: <3C924680.4090806@openratings.com>
	<002801c1cc4f$25dba980$7889ffcc@comstock.com>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <title></title>
</head>
<body>
Gregory Wood wrote:<br>
<blockquote type="cite" cite="mid:002801c1cc4f$25dba980$7889ffcc@comstock.com">
  <blockquote type="cite">
    <pre wrap="">explain select * from a where x=3;<br></pre>
    </blockquote>
    <pre wrap=""><!----><br>PostgreSQL is treating 3 as an int4 (integer) type, whereas x is an int2<br>(smallint) type. Try casting the constant as a smallint and it should use<br>the index:<br><br>explain select * from a where x=3::smallint;<br><br></pre>
    </blockquote>
Aha!  Great! Thanks a lot! That worked!<br>
Now, the next problem:<br>
    <br>
explain select count (x) from a ;<br>
    <br>
Aggregate  (cost=100175934.05..100175934.05 rows=1 width=2)<br>
  -&gt;  Seq Scan on a  (cost=100000000.00..100150659.04 rows=10110004 width=2)<br>
    <br>
Am I missing something here again, or will it just not use an index for aggregation?<br>
    <br>
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...<br>
    <br>
    <br>
    <br>
    </body>
    </html>




view thread (11+ messages)  latest in thread

Message-ID: <3C92530D.2050005@openratings.com>
Permalink:  ../3C92530D.2050005@openratings.com/
Also on:    postgresql.org/message-id/3C92530D.2050005@openratings.com

 · 

reply

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: dmitry@openratings.com, gregw@com-stock.com, pgsql-general@postgresql.org
  Subject: Re: Why is it not using an index?
  In-Reply-To: <3C92530D.2050005@openratings.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