Cees van de Griend <cees-list@griend.xs4all.nl> writes:
> The strange part is that a query on the first database takes 2.5 seconds
> and on the second one 3 minutes and 7.1 second!
> EXPLAIN looks the same on both databases.
No, it doesn't look the same at all: you're getting hash joins in one
case and nestloop joins in the other. I'd also observe that you haven't
given us anything close to an accurate version of the query, as the
EXPLAINs show four input tables not two. Moreover, the two EXPLAINs
are clearly not for the same query (the table names aren't the same).
> -> Hash Join (cost=20.38..1145.68 rows=1161 width=40)
> -> Seq Scan on tblcitsdialinday dd (cost=0.00..1006.03 rows=2485 width=32)
> -> Hash (cost=18.10..18.10 rows=910 width=8)
> -> Seq Scan on tblcitsddinumber dn (cost=0.00..18.10 rows=910 width=8)
vs
> -> Nested Loop (cost=0.00..1119.65 rows=4 width=40)
> -> Seq Scan on tblatnnumber dn (cost=0.00..10.03 rows=503 width=8)
> -> Index Scan using idxatndialinday04 on tblatndialinday dd (cost=0.00..2.19 rows=1
width=32)
The problem is presumably that the planner is drastically
underestimating the number of joinable rows in "dn" in the
second case, and so choosing a plan that works well if that
number is small but not well when it's large. There's not
a lot you can do about this in 7.0.3, but you are overdue
for an update anyway. I'd suggest trying it in 7.2, due
out Monday.
regards, tom lane