i've got two machines both with roughly the same database, one has a few
more rows in some of the tables, but only about 2% more. both databases
have the same tables and the same indexes. however, the slower machine
seemed to be much slower than i would have expected it to be, so i
started doing some looking, and found some very different and strange
query plans. below is the query and the plans:
explain
SELECT DISTINCT Org.ID
FROM Org AS Org, OrgContactRelation AS tm2
WHERE Org.ID = tm2.OrgID AND tm2.ContactID = 1
ORDER BY Org.ID ASC
;
fast machine:
Unique (cost=4.33..4.33 rows=1 width=8) -> Sort (cost=4.33..4.33 rows=1 width=8) -> Nested Loop
(cost=0.00..4.32rows=1 width=8) -> Index Scan using orgcontactrelation_pkey on orgcontactrelation tm2
(cost=0.00..2.05rows=1 width=4) -> Index Scan using org_pkey on org (cost=0.00..2.26 rows=1 width=4)
slow machine:
Unique (cost=231.34..4145.04 rows=12435 width=8) -> Merge Join (cost=231.34..3834.15 rows=124353 width=8) ->
IndexScan using org_pkey on org (cost=0.00..3162.52 rows=34867 width=4) -> Sort (cost=231.34..231.34 rows=357
width=4) -> Index Scan using orgcontactrelation_pkey on orgcontactrelation tm2 (cost=0.00..216.22 rows=357
width=4)
what i think is causing the basic problem is the way, way off row
estimates that the slow machine seems to be making. the index scan of
orgcontactrelation_pkey on the fast machine estimated one row, which is
pretty close because although there are some 36 000 rows, there are only
a handful of duplicate contactid rows. but for some reason, the slow
machine estimates 357 rows, then just baloons the estimates up from
there.
now, this particular query returns quite quickly on both machines, but
it demonstrates a problem that causes another query to take 10 seconds
on the slow one and < .10 on the fast one. if needed, i can provide the
more complex query and plans.
i'm really quite confused. both databases have been vacuumed, the
indexes/tables are the same, the layout of the data in the rows is very
equal, yet this large discrepancy.
just this very instant, i realized one thing. i had built the database
on the slow machine using a java program to extract it from db2 and
insert it. then, i dumped the database from the slow machine and loaded
it up on the fast machine. i wonder if that might have caused the
difference.
anyway, any assistance in understanding this would be greatly
appreciated. also, if this looks like a problem with postgres, i'd be
happy to run/test anything. this is just a test database, so i'm not
worried about losing or damaging it.
this is on postgresql 7.1.2, the database is large (about 700MB
according to du ), and everything is freshly vacuumed.
--
CraigL->Thx(); Be Developer ID: 5852