pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: gherzig@fmed.uba.ar
To: Scott Marlowe <smarlowe@g2switchworks.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: index not being used. Why?
Date: Fri, 9 Mar 2007 17:35:11 -0300 (ART)
Message-ID: <27676.190.55.116.36.1173472511.squirrel@www.webmail.fmed.uba.ar> (raw)
In-Reply-To: <1173470311.20651.302.camel@state.g2switchworks.com>
References: <45F176CA.3050107@fmed.uba.ar>
	<1173470311.20651.302.camel@state.g2switchworks.com>

Thanks all you guys. Indeed, populating the tables with 10.000 entrys make
the things different, and now it uses all the indexes as i spect. It was
just a matter of being pacient and loading more data to test it out and
see.

And, yes, i need to upgrade psql now. Actually the real server has an
8.2.0 engine.

Thanks all you guys!
Gerardo
> On Fri, 2007-03-09 at 09:01, Gerardo Herzig wrote:
>> Hi all. I have this 2 relations
>>
>
> SNIP
>
>>                Index Cond: ((upper((word)::text) ~>=~ 'TEST'::character
>> varying) AND (upper((word)::text) ~<~'TESU'::character
>> varying))
>>    ->  Hash  (cost=9.08..9.08 rows=408 width=55)
>>          ->  Seq Scan on pages  (cost=0.00..9.08 rows=408 width=55)
>>
>>  (8 rows)
>>
>>
>> Watch the last row of the explain command. It makes a sequential scan
>> on the pages table, like it is not using the index on the "id" field.
>>
>> The result itself is OK, but i will populate the tables so i think
>> that later that sequential scan would be a problem.
>
> Welcome to the world of tomorrow!  hehe.  PostgreSQL uses a cost based
> planner.  It decided that an index would cost more than a seq scan, so
> it chose the seq scan.  As mentioned in other posts, you'll need to do
> an analyze.  Also, look up things like vacuum / autovacuum as well.
>
>> Im using postgres 8.1.3
>
> You need to upgrade to 8.1.8 or whatever the latest version is by the
> time this email gets to you :)  8.1.3 is about a year out of date.
>
>
>


-- 
Gerardo Herzig
Direccion General de Organizacion y Sistemas
Facultad de Medicina
U.B.A.



view thread (7+ messages)

Message-ID: <27676.190.55.116.36.1173472511.squirrel@www.webmail.fmed.uba.ar>
Permalink:  ../27676.190.55.116.36.1173472511.squirrel@www.webmail.fmed.uba.ar/
Also on:    postgresql.org/message-id/27676.190.55.116.36.1173472511.squirrel@www.webmail.fmed.uba.ar

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: gherzig@fmed.uba.ar, smarlowe@g2switchworks.com
  Subject: Re: index not being used. Why?
  In-Reply-To: <27676.190.55.116.36.1173472511.squirrel@www.webmail.fmed.uba.ar>
  References: <45F176CA.3050107@fmed.uba.ar> <1173470311.20651.302.camel@state.g2switchworks.com> <27676.190.55.116.36.1173472511.squirrel@www.webmail.fmed.uba.ar>

* 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