Re: [GENERAL] Indexes not working (bug in 7.0.2?)
Manual says you should use it after initial data entry, after entering a large amount of rows and periodically. At 23:02 4.9.2000 , Zlatko Calusic wrote: Tom Lane [EMAIL PROTECTED] writes: Zlatko Calusic [EMAIL PROTECTED] writes: It is now NOT using the index, and I don't understand why? Queries are practically the same, tables are practically the same, why is postgres using indexes in the first case and not in the second? Because it has substantially different ideas about the sizes of the two tables --- notice the different estimated row counts. If you haven't "vacuum analyzed" these tables recently, do so to bring the planner's statistics up-to-date, and then see what you get. You may also care to read the user's manual chapter about EXPLAIN, http://www.postgresql.org/users-lounge/docs/7.0/postgres/c4888.htm Yes, thanks to all who helped. 'vacuum analyze' was the magical incantation that helped. I still have one uncertainty. Is it possible that after some time postgres once again decides not to use indices (assuming I haven't run 'vacuum analyze' again)? -- Zlatko
Re: [GENERAL] Indexes not working (bug in 7.0.2?)
Tom Lane [EMAIL PROTECTED] writes: Zlatko Calusic [EMAIL PROTECTED] writes: It is now NOT using the index, and I don't understand why? Queries are practically the same, tables are practically the same, why is postgres using indexes in the first case and not in the second? Because it has substantially different ideas about the sizes of the two tables --- notice the different estimated row counts. If you haven't "vacuum analyzed" these tables recently, do so to bring the planner's statistics up-to-date, and then see what you get. You may also care to read the user's manual chapter about EXPLAIN, http://www.postgresql.org/users-lounge/docs/7.0/postgres/c4888.htm Yes, thanks to all who helped. 'vacuum analyze' was the magical incantation that helped. I still have one uncertainty. Is it possible that after some time postgres once again decides not to use indices (assuming I haven't run 'vacuum analyze' again)? -- Zlatko
[GENERAL] Indexes not working (bug in 7.0.2?)
Hi! This is my first post (not counting those failed because I posted them from the other email address) so please be gentle. I have recently started playing with PostgreSQL and found what I think is a bug in postgres. I'm using 7.0.2 from Debian Linux package (woody) if its of any difference. I have a table like this: imenik=# \d subscriber Table "subscriber" Attribute | Type | Modifier ---+--+-- id| integer | not null prefix| char(2) | not null name | varchar(128) | not null number| varchar(8) | not null title | integer | street| integer | not null num | varchar(8) | not null city | integer | not null Index: idx_number First I populated the table, then I created index on the number field with "CREATE INDEX idx_number on subscriber(number);" The trouble is when I do SELECT specifying a number, postgres reads a whole table ie. it is not using the index. EXPLAIN reveals it is indeed doing a sequential scan. test=# EXPLAIN SELECT * from subscriber where number = '123456'; NOTICE: QUERY PLAN: Seq Scan on subscriber (cost=0.00..38677.28 rows=15564 width=64) EXPLAIN What am I doing wrong??? Second example: Today I stumbled upon a similar problem with completely different set of data, but even more confusing. Consider two tables 'filenew' and 'fileold' that have same fields and indices: filedb=# \d fileold Table "fileold" Attribute | Type | Modifier ---+---+-- file | varchar(1024) | not null mode | integer | not null uid | integer | not null gid | integer | not null size | bigint| not null mtime | integer | not null ctime | integer | not null Index: fileold_file_key s/fileold/filenew and you know the schema for filenew. Idea is to populate fileold once with filesystem information and then later, when things on FS change, populate filenew with a new data and search for differences. As you see, tables are almost the same, but... *** Looking for new files: filedb=# explain select file from filenew where not exists (select 1 from fileold where filenew.file = fileold.file); NOTICE: QUERY PLAN: Seq Scan on filenew (cost=0.00..0.00 rows=1 width=12) SubPlan - Index Scan using fileold_file_key on fileold (cost=0.00..2935.96 rows=1329 width=4) EXPLAIN *** Looking for deleted files: filedb=# explain select file from fileold where not exists (select 1 from filenew where fileold.file = filenew.file); NOTICE: QUERY PLAN: Seq Scan on fileold (cost=0.00..3155.26 rows=1 width=12) SubPlan - Seq Scan on filenew (cost=0.00..0.00 rows=1 width=4) EXPLAIN It is now NOT using the index, and I don't understand why? Queries are practically the same, tables are practically the same, why is postgres using indexes in the first case and not in the second? TIA, -- Zlatko
Re: [GENERAL] Indexes not working (bug in 7.0.2?)
* Zlatko Calusic [EMAIL PROTECTED] [000903 07:59] wrote: Hi! This is my first post (not counting those failed because I posted them from the other email address) so please be gentle. I have recently started playing with PostgreSQL and found what I think is a bug in postgres. I'm using 7.0.2 from Debian Linux package (woody) if its of any difference. I have a table like this: [snip] Two things: Try "vacuum analyze subscriber;" if that doesn't work you may want to try "set enable seqscan off;" -Alfred
Re: [GENERAL] Indexes not working (bug in 7.0.2?)
Zlatko Calusic [EMAIL PROTECTED] writes: It is now NOT using the index, and I don't understand why? Queries are practically the same, tables are practically the same, why is postgres using indexes in the first case and not in the second? Because it has substantially different ideas about the sizes of the two tables --- notice the different estimated row counts. If you haven't "vacuum analyzed" these tables recently, do so to bring the planner's statistics up-to-date, and then see what you get. You may also care to read the user's manual chapter about EXPLAIN, http://www.postgresql.org/users-lounge/docs/7.0/postgres/c4888.htm regards, tom lane
