On Sun, 2005-03-13 at 16:32 +0100, Miroslav Šulc wrote: > Hi all, > > I am new to PostgreSQL and query optimizations. We have recently moved > our project from MySQL to PostgreSQL and we are having performance > problem with one of our most often used queries. On MySQL the speed was > sufficient but PostgreSQL chooses time expensive query plan. I would > like to optimize it somehow but the query plan from EXPLAIN ANALYZE is > little bit cryptic to me. >
[snip output of EXPLAIN ANALYZE] for those of us who have not yet reached the level where one can infer it from the query plan, how abour showing us the actual query too ? but as an example of what to look for, consider the first few lines (reformatted): > Merge Right Join (cost=9868.84..9997.74 rows=6364 width=815) > (actual time=9982.022..10801.216 rows=6364 loops=1) > Merge Cond: ("outer".idpk = "inner".cadastralunitidfk) > -> Index Scan using cadastralunits_pkey on cadastralunits > (cost=0.00..314.72 rows=13027 width=31) > (actual time=0.457..0.552 rows=63 loops=1) > -> Sort (cost=9868.84..9884.75 rows=6364 width=788) > (actual time=9981.405..10013.708 rows=6364 loops=1) notice that the index scan is expected to return 13027 rows, but actually returns 63. this might influence the a choice of plan. gnari ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]