pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedindex not being used. Why?
7+ messages / 6 participants
[nested] [flat]
* index not being used. Why?
@ 2007-03-09 15:01 Gerardo Herzig <gherzig@fmed.uba.ar>
0 siblings, 4 replies; 7+ messages in thread
From: Gerardo Herzig @ 2007-03-09 15:01 UTC (permalink / raw)
To: pgsql-sql
Hi all. I have this 2 relations
gse=# \d pages
Table "public.pages"
Column | Type | Modifiers
---------------------+-------------------+----------------------------------------------------
id | integer | not null default nextval('pages_id_seq'::regclass)
fullpath | character varying |
last_modified_stamp | bigint |
title | character varying |
Indexes:
"pages_pkey" PRIMARY KEY, btree (id)
"pages_fullpath_idx" UNIQUE, btree (fullpath)
"pages_id_idx" btree (id)
gse=# \d words
Table "public.words"
Column | Type | Modifiers
---------------+-------------------+-----------
page_id | integer |
word | character varying |
word_position | integer |
Indexes:
"words_idx" btree (word)
"words_page_id_idx" btree (page_id)
"words_page_id_word_position_id" btree (page_id, word_position)
"words_upper_idx" btree (upper(word::text) varchar_pattern_ops)
Now, when i execute
gse=# explain select * from words, pages where words.page_id = pages.id and upper(word) like 'TEST%';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=18.29..916.33 rows=698 width=72)
Hash Cond: ("outer".page_id = "inner".id)
-> Bitmap Heap Scan on words (cost=8.19..885.64 rows=698 width=17)
Filter: (upper((word)::text) ~~ 'TEST%'::text)
-> Bitmap Index Scan on words_upper_idx (cost=0.00..8.19 rows=698 width=0)
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.
I have not idea why this is happening, hope you guys could give me a clue or make me understand the situation.
Im using postgres 8.1.3
Thanks!
Gerardo
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: index not being used. Why?
@ 2007-03-09 15:59 Richard Huxton <dev@archonet.com>
parent: Gerardo Herzig <gherzig@fmed.uba.ar>
3 siblings, 0 replies; 7+ messages in thread
From: Richard Huxton @ 2007-03-09 15:59 UTC (permalink / raw)
To: Gerardo Herzig <gherzig@fmed.uba.ar>; +Cc: pgsql-sql
Gerardo Herzig wrote:
> -> Seq Scan on pages (cost=0.00..9.08 rows=408 width=55)
> 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.
You only have 408 rows in the table - it's probably not worth the
trouble of using an index and *then* fetching the rows. Especially since
it's going to match most of the pages anyway.
Try adding a few thousand rows, analyse and see if it decides to use the
index then.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: index not being used. Why?
@ 2007-03-09 16:02 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Gerardo Herzig <gherzig@fmed.uba.ar>
3 siblings, 0 replies; 7+ messages in thread
From: Andrew Sullivan @ 2007-03-09 16:02 UTC (permalink / raw)
To: pgsql-sql
On Fri, Mar 09, 2007 at 12:01:30PM -0300, Gerardo Herzig wrote:
> Hi all. I have this 2 relations
How big are they?
> -> Hash (cost=9.08..9.08 rows=408 width=55)
> -> Seq Scan on pages (cost=0.00..9.08 rows=408 width=55)
The planner thinks it will get 408 rows. How big a percentage of the
table is that?
Also, what does EXPLAIN ANALYSE say about this?
A
--
Andrew Sullivan | ajs@crankycanuck.ca
If they don't do anything, we don't need their acronym.
--Josh Hamilton, on the US FEMA
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: index not being used. Why?
@ 2007-03-09 16:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: Gerardo Herzig <gherzig@fmed.uba.ar>
3 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2007-03-09 16:29 UTC (permalink / raw)
To: Gerardo Herzig <gherzig@fmed.uba.ar>; +Cc: pgsql-sql
Gerardo Herzig <gherzig@fmed.uba.ar> writes:
> 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.
Why do you think that the plans won't change when the tables get bigger?
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: index not being used. Why?
@ 2007-03-09 16:39 Gerardo Herzig <gherzig@fmed.uba.ar>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Gerardo Herzig @ 2007-03-09 16:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Someday i will ask some question that will makes you think a little
more. You'll see.
I will populate the tables and redo the explain.
Thanks dudes!!
Gerardo
>Gerardo Herzig <gherzig@fmed.uba.ar> writes:
>
>
>>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.
>>
>>
>
>Why do you think that the plans won't change when the tables get bigger?
>
> regards, tom lane
>
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: index not being used. Why?
@ 2007-03-09 19:58 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Gerardo Herzig <gherzig@fmed.uba.ar>
3 siblings, 1 reply; 7+ messages in thread
From: Scott Marlowe @ 2007-03-09 19:58 UTC (permalink / raw)
To: Gerardo Herzig <gherzig@fmed.uba.ar>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: index not being used. Why?
@ 2007-03-09 20:35 gherzig@fmed.uba.ar
parent: Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 0 replies; 7+ messages in thread
From: gherzig@fmed.uba.ar @ 2007-03-09 20:35 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2007-03-09 20:35 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-09 15:01 index not being used. Why? Gerardo Herzig <gherzig@fmed.uba.ar>
2007-03-09 15:59 ` Richard Huxton <dev@archonet.com>
2007-03-09 16:02 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-03-09 16:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-03-09 16:39 ` Gerardo Herzig <gherzig@fmed.uba.ar>
2007-03-09 19:58 ` Scott Marlowe <smarlowe@g2switchworks.com>
2007-03-09 20:35 ` gherzig@fmed.uba.ar
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