strange plan
От | Craig Longman |
---|---|
Тема | strange plan |
Дата | |
Msg-id | 993619683.6506.12.camel@jigra.begeek.com обсуждение исходный текст |
Ответы |
Re: strange plan
(Craig Longman <craigl@begeek.com>)
session detect and terminate (Jie Liang <jliang@ipinc.com>) |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: