cpu_tuple_cost WRONG -> After an "analyze" or "vacuum full" indexes are not used anymore!!! - Mailing list pgsql-admin
From | Gaetano Mendola |
---|---|
Subject | cpu_tuple_cost WRONG -> After an "analyze" or "vacuum full" indexes are not used anymore!!! |
Date | |
Msg-id | 014c01c2361e$62a53c30$1aadd6c2@GMENDOLA2 Whole thread Raw |
Responses |
Re: cpu_tuple_cost WRONG -> After an "analyze" or "vacuum full" indexes are not used anymore!!!
|
List | pgsql-admin |
Hi all, I did a little experiment ( Postgres 7.2.1 ): I inserted some rows (28896) in an table with random value beetwen 0 and 100 and after same explain I noticed that the index created is not used after a vacuum analyze ( some times after a vacuum full ) I raise the cpu_tuple_cost and the behaviour seem correct, this what I did: kalman=# create table to_del (col1 serial primary key, col2 integer); NOTICE: CREATE TABLE will create implicit sequence 'to_del_col1_seq' for SERIAL column 'to_del.col1' NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'to_del_pkey' for table 'to_del' CREATE kalman=# insert into to_del(col2) (select (random()*100)::integer); NOTICE: to_del_col1_seq.nextval: sequence was re-created INSERT 5008207 1 kalman=# insert into to_del(col2) (select (random()*100)::integer from to_del); INSERT 5008208 1 kalman=# insert into to_del(col2) (select (random()*100)::integer from to_del t1, to_del t2); INSERT 0 4 kalman=# insert into to_del(col2) (select (random()*100)::integer from to_del t1, to_del t2); INSERT 0 36 kalman=# insert into to_del(col2) (select (random()*100)::integer from to_del t1, to_del t2); INSERT 0 1764 kalman=# insert into to_del(col2) (select (random()*100)::integer from to_del t1); INSERT 0 1806 kalman=# insert into to_del(col2) (select (random()*100)::integer from to_del t1); INSERT 0 3612 kalman=# insert into to_del(col2) (select (random()*100)::integer from to_del t1); INSERT 0 7224 kalman=# insert into to_del(col2) (select (random()*100)::integer from to_del t1); INSERT 0 14448 kalman=# select count(*) from to_del; count ------- 28896 (1 row) kalman=# select count(*) from to_del where col2 = 30; count ------- 283 (1 row) kalman=# explain select * from to_del where col2 = 30; NOTICE: QUERY PLAN: Seq Scan on to_del (cost=0.00..22.50 rows=5 width=8) EXPLAIN kalman=# create index idx_col2 on to_del(col2); CREATE kalman=# explain select * from to_del where col2 = 30; NOTICE: QUERY PLAN: Index Scan using idx_col2 on to_del (cost=0.00..399.75 rows=144 width=8) EXPLAIN kalman=# analyze; ANALYZE kalman=# explain select * from to_del where col2 = 30; NOTICE: QUERY PLAN: Seq Scan on to_del (cost=0.00..520.06 rows=277 width=8) EXPLAIN kalman=# vacuum full; VACUUM kalman=# explain select * from to_del where col2 = 30; NOTICE: QUERY PLAN: Seq Scan on to_del (cost=0.00..518.20 rows=275 width=8) EXPLAIN kalman=# analyze; ANALYZE kalman=# explain select * from to_del where col2 = 30; NOTICE: QUERY PLAN: Seq Scan on to_del (cost=0.00..520.06 rows=407 width=8) EXPLAIN So it's really strange. I continued with this consideration: For a seq scan the cost is 520.06 kalman=# set enable_seqscan=off; SET VARIABLE kalman=# explain select * from to_del where col2 = 30; NOTICE: QUERY PLAN: Index Scan using idx_col2 on to_del (cost=0.00..634.38 rows=407 width=8) so for an index scan is 634.38 kalman=# show cpu_tuple_cost; NOTICE: cpu_tuple_cost is 0.01 SHOW VARIABLE kalman=# show cpu_index_tuple_cost ; NOTICE: cpu_index_tuple_cost is 0.001 SHOW VARIABLE So I tried to raise the cost of cpu_tuple_cost: kalman=# set cpu_tuple_cost = 0.1; SET VARIABLE and now all seems right: kalman=# explain select * from to_del where col2 = 30; NOTICE: QUERY PLAN: Index Scan using idx_col2 on to_del (cost=0.00..670.97 rows=407 width=8) now I'll check if the scan is used again: kalman=# update to_del set col2 = 30; UPDATE 28896 kalman=# explain select * from to_del where col2 = 30; NOTICE: QUERY PLAN: Index Scan using idx_col2 on to_del (cost=0.00..670.97 rows=407 width=8) and after an analyze, correctly I obtain: kalman=# analyze; ANALYZE kalman=# explain select * from to_del where col2 = 30; NOTICE: QUERY PLAN: Seq Scan on to_del (cost=0.00..858.40 rows=5321 width=8) EXPLAIN Some time the optimizer is right to use the seq scan instead of an index I tried to insert 10^6 rows and the time performance are completly different: 6 seconds with cpu_tuple_cost = 0.01 and 0.6 seconds with cpu_tuple_cost = 0.1 I'm going to set the parameter cpu_tuple_cost = 0.1 to my server, I'm wrong ? PS: in an Postgres 7.1.3 box the behaviour is correct: the index is used also after an analyze; Ciao Gaetano -- #exclude <windows> #include <CSRSS> printf("\t\t\b\b\b\b\b\b");. printf("\t\t\b\b\b\b\b\b");
pgsql-admin by date: