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
