Обсуждение: index not being used. Why?
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.64rows=698 width=17) Filter: (upper((word)::text) ~~ 'TEST%'::text) -> Bitmap Index Scan
onwords_upper_idx (cost=0.00..8.19 rows=698 width=0) Index Cond: ((upper((word)::text) ~>=~
'TEST'::charactervarying) 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
indexon 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
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
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
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
indexon 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
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 indexon 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 > > > >
Hi Everybody,
We are using postgres 8.1.0. I want to do some maintenance work.
Hence, I want to run postgres in single user mode so that external people
won't be able to access the database.
How can I run the postgres in single user mode?. Any idea?
Regards
skarthi
_________________________________________________________________
Get a FREE Web site, company branded e-mail and more from Microsoft Office
Live! http://clk.atdmt.com/MRT/go/mcrssaub0050001411mrt/direct/01/
On 3/9/07, Karthikeyan Sundaram <skarthi98@hotmail.com> wrote: > Hi Everybody, > > We are using postgres 8.1.0. I want to do some maintenance work. > Hence, I want to run postgres in single user mode so that external people > won't be able to access the database. > > How can I run the postgres in single user mode?. Any idea? > > Regards > skarthi See: http://www.postgresql.org/docs/8.2/static/app-postgres.html
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'::charactervarying)) > -> 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.
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.