Re: More Performance

Hi,

I've found another one of these performance problems in the benchmark,
related to another ignored index.

The whole thing works perfectly after a VACUUM ANALYZE on the
table.

IMHO this is somewhat non-optimal. In the absence of information
to the contrary, PostgreSQL should default to using an index if
it might be appropriate, not ignore it.

I am thus throwing away yet another benchmark run -- the query now runs
300 times faster. *Sigh* 

test=# vacuum bench1;
VACUUM
test=# \d bench1        Table "bench1"Attribute |   Type   | Modifier 
-----------+----------+----------id        | integer  | not nullid2       | integer  | not nullid3       | integer  |
notnulldummy1    | char(30) | 
 
Indices: bench1_index_,        bench1_index_1

test=# \d bench1_index_


Index "bench1_index_"Attribute |  Type   
-----------+---------id        | integerid2       | integer
unique btree

test=# 
test=# 
test=# \d bench1_index_1
Index "bench1_index_1"Attribute |  Type   
-----------+---------id3       | integer
btree

test=# explain update bench1 set dummy1='updated' where id=150;
NOTICE:  QUERY PLAN:

Seq Scan on bench1  (cost=0.00..6843.00 rows=3000 width=18)

EXPLAIN
test=# vacuum bench1;
VACUUM
test=# explain update bench1 set dummy1='updated' where id=150;
NOTICE:  QUERY PLAN:

Seq Scan on bench1  (cost=0.00..6843.00 rows=3000 width=18)

EXPLAIN
test=# select count(*) from bench1;count  
--------300000
(1 row)

test=# select count(*) from bench1 where id = 150;count 
-------    1
(1 row)

test=# explain select count(*) from bench1 where id = 150;
NOTICE:  QUERY PLAN:

Aggregate  (cost=6850.50..6850.50 rows=1 width=4) ->  Seq Scan on bench1  (cost=0.00..6843.00 rows=3000 width=4)

EXPLAIN


***************************************************************

Related to this:

test=# explain select id from bench1 order by id;
NOTICE:  QUERY PLAN:

Sort  (cost=38259.21..38259.21 rows=300000 width=4) ->  Seq Scan on bench1  (cost=0.00..6093.00 rows=300000 width=4)

EXPLAIN

The basic idea to speed this one up (a lot...) would be to walk the index.

This is _after_ ANALYZE, of course.

-- 
Matthias Urlichs  |  noris network GmbH   |   smurf@noris.de  |  ICQ: 20193661
The quote was selected randomly. Really.       |        http://smurf.noris.de/
-- 
To be positive: To be mistaken at the top of one's voice.               -- Ambrose Bierce, The Devil's Dictionary