Same database, different query plans

From: WireSpot <wirespot(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Same database, different query plans
Date: 2005-08-18 09:03:59
Message-ID: b2d4b038050818020371508ebe@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have the exact same database on two different systems, both using
8.0.3. I've installed the database from the same dump. Here's the
query I'm trying on each:

EXPLAIN ANALYZE SELECT
answers.*,i18n.field1,i18n.field2,i18n.field3,i18n.field4,i18n.field5
FROM answers LEFT OUTER JOIN i18n on answers.i18n_id = i18n.id and
i18n.languages_id = 2 WHERE question_id = 2938 ORDER BY display_order;

Here's the result from one of them:

-------------------------------------------
Sort (cost=30.46..30.47 rows=2 width=125) (actual time=0.110..0.111
rows=1 loops=1)
Sort Key: answers.display_order
-> Nested Loop Left Join (cost=0.00..30.45 rows=2 width=125)
(actual time=0.081..0.088 rows=1 loops=1)
-> Index Scan using question_id_answers_key on answers
(cost=0.00..9.07 rows=2 width=38) (actual time=0.042..0.047 rows=1
loops=1)
Index Cond: (question_id = 2938)
-> Index Scan using i18n_pkey on i18n (cost=0.00..10.68
rows=1 width=91) (actual time=0.019..0.019 rows=0 loops=1)
Index Cond: ("outer".i18n_id = i18n.id)
Filter: (languages_id = 2)
Total runtime: 0.306 ms
(9 rows)
-------------------------------------------

And the other:

-------------------------------------------
Sort (cost=1025.08..1025.14 rows=22 width=223) (actual
time=397.154..397.155 rows=1 loops=1)
Sort Key: answers.display_order
-> Merge Left Join (cost=1023.34..1024.59 rows=22 width=223)
(actual time=396.695..396.700 rows=1 loops=1)
Merge Cond: ("outer".i18n_id = "inner".id)
-> Sort (cost=71.81..71.86 rows=22 width=63) (actual
time=0.346..0.349 rows=1 loops=1)
Sort Key: answers.i18n_id
-> Index Scan using question_id_answers_key on answers
(cost=0.00..71.31 rows=22 width=63) (actual time=0.320..0.327 rows=1
loops=1)
Index Cond: (question_id = 2938)
-> Sort (cost=951.53..952.00 rows=187 width=164) (actual
time=375.092..385.246 rows=5651 loops=1)
Sort Key: i18n.id
-> Seq Scan on i18n (cost=0.00..944.48 rows=187
width=164) (actual time=0.127..132.919 rows=10940 loops=1)
Filter: (languages_id = 2)
Total runtime: 398.751 ms
(13 rows)
-------------------------------------------

The actual SELECT results (ie. non EXPLAIN) are identical in both
cases. The indexes and so on are identical. I've done a reindexing and
vacuuming on both of them just to be sure.

As you can see, there's quite a bit of a difference between 0.3 ms and
398 ms, and it shows. I haven't touched the query planning options.
Why the different planning and what can I do to fix the misguided one?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Aliomar Mariano Rego 2005-08-18 10:12:55 Re: Set autocommit to off
Previous Message Marko Kreen 2005-08-18 08:10:54 Re: Adding contrib modules