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 null
id2 | integer | not null
id3 | integer | not null
dummy1 | char(30) |
Indices: bench1_index_,
bench1_index_1

test=# \d bench1_index_

Index "bench1_index_"
Attribute | Type
-----------+---------
id | integer
id2 | 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(at)noris(dot)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