pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Martijn van Oosterhout <kleptog@svana.org>
To: Jean-Luc Lachance <jllachan@nsd.ca>
Cc: Gregory Wood <gregw@com-stock.com>
Cc: Dmitry Tkach <dmitry@openratings.com>
Cc: PostgreSQL-General <pgsql-general@postgresql.org>
Subject: Re: Why is it not using an index?
Date: Mon, 18 Mar 2002 10:55:45 +1100
Message-ID: <20020318105545.A24566@svana.org> (raw)
In-Reply-To: <3C9258CB.B22E190A@nsd.ca>
References: <3C924680.4090806@openratings.com>
	<002801c1cc4f$25dba980$7889ffcc@comstock.com>
	<3C9258CB.B22E190A@nsd.ca>

On Fri, Mar 15, 2002 at 03:25:47PM -0500, Jean-Luc Lachance wrote:
> Really, the PostgreSQL interpreter should be smart enough to figure this
> out by itself...

It is actually, if you put quotes around the number so it is explicitly
typed as 'unknown'. The interpreter will then accuratly match the type.
Without the quotes the number becomes int4 and so a whole promotion/type
hierarchy needs to be built to determine how to relate them.

Just put quotes around all your constants and all your problems are solved.

> 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 use
> > the index:
> > 
> > explain select * from a where x=3::smallint;
> > 
> > Greg
> > 
> > ----- Original Message -----
> > From: "Dmitry Tkach" <dmitry@openratings.com>
> > To: <pgsql-general@postgresql.org.pgsql-sql@postgresql.org>
> > Sent: Friday, March 15, 2002 2:07 PM
> > Subject: [GENERAL] Why is it not using an index?
> > 
> > > This must be really simple, but I just can't get it :-(
> > > I have a table (a) with a single column (x):
> > >
> > >            Table "a"
> > >   Attribute |   Type   | Modifier
> > > -----------+----------+----------
> > >   x         | smallint |
> > > Index: a_idx
> > >
> > >
> > >     Index "a_idx"
> > >   Attribute |   Type
> > > -----------+----------
> > >   x         | smallint
> > > btree
> > >
> > > The table has 10000000 rows....
> > >
> > > Now, how come, when I do:
> > >
> > > explain select * from a where x=3;
> > >
> > > it says:
> > >
> > > Seq Scan on bset  (cost=100000000.00..100175934.05 rows=303 width=2)
> > >
> > > Why is it not using a_idx???
> > >
> > > I even tried set enable_seqscan to off - makes no difference :-(
> > >
> > > Any idea what is going on?
> > >
> > > Thanks a lot!
> > >
> > > Dima
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 5: Have you checked our extensive FAQ?
> > >
> > > http://www.postgresql.org/users-lounge/docs/faq.html
> > >
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> > 
> > http://www.postgresql.org/users-lounge/docs/faq.html
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

-- 
Martijn van Oosterhout <kleptog@svana.org>   http://svana.org/kleptog/
> Ignorance continues to thrive when intelligent people choose to do
> nothing.  Speaking out against censorship and ignorance is the imperative
> of all intelligent people.



view thread (11+ messages)

Message-ID: <20020318105545.A24566@svana.org>
Permalink:  ../20020318105545.A24566@svana.org/
Also on:    postgresql.org/message-id/20020318105545.A24566@svana.org

 · 

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: kleptog@svana.org, jllachan@nsd.ca, gregw@com-stock.com, dmitry@openratings.com, pgsql-general@postgresql.org
  Subject: Re: Why is it not using an index?
  In-Reply-To: <20020318105545.A24566@svana.org>

* 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