CREATE TABLE cddb ( id integer PRIMARY KEY,
/* artist name */ name text,
/* disk title */ title text,
/* the type of the artist group, person, orchestra*/ type smallint,
/* the creation date */ created integer );
CREATE INDEX cddb1 on cddb(title); CREATE INDEX cddb2 on cddb(name);
CREATE TABLE cddbentry ( id integer PRIMARY KEY,
diskid text,
entry integer REFERENCES cddb );
CREATE INDEX cddbentry1 on cddbentry (diskid); CREATE INDEX cddbentry2 on cddbentry (entry);
I am using the following query:
select cddb.* from cddb,cddbentry where cddbentry.diskid = 'toto' and cddbentry.entry = cddb.id;
On version 7.3.4 it produces this query plan
Nested Loop (cost=0.00..41.25 rows=5 width=78)
-> Index Scan using cddbentry1 on cddbentry (cost=0.00..17.07 rows=5 width=4)
Index Cond: (diskid = 'toto'::text)
-> Index Scan using cddb_pkey on cddb (cost=0.00..4.82 rows=1 width=74)
Index Cond: ("outer".entry = cddb.id)
I have upgraded to version 7.4.0 (compiling the software and migrating the database using dump/restore)
Now the following plan is produced
Hash Join (cost=17.08..42.15 rows=7 width=74)
Hash Cond: ("outer".id = "inner".entry)
-> Seq Scan on cddb (cost=0.00..20.00 rows=1000 width=74)
-> Hash (cost=17.07..17.07 rows=6 width=4)
-> Index Scan using cddbentry1 on cddbentry (cost=0.00..17.07 rows=6 width=4)
Index Cond: (diskid = 'toto'::text)
which result in a VERY much slower query as the cddb table has more than 1 million entry.... and there is at most one entry in cddbentry
which matches the diskid !
The workaround is to disable the hash join capability using set enable_hashjoin to false; resulting in the same query plan as in 7.3.x.
I think this can classified as a regression bug.
N.B: The cost value may be wrong, because to be able to send you this email, they have been produced on empty tables; but
I can guarantee you they are the same on the full loaded database.
Hope this help to improve this great product
Bernard SNYERS
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
