query doesn't always follow 'correct' path..

From: Bert <biertie(at)gmail(dot)com>
To: pgsql-sql <pgsql-sql(at)postgresql(dot)org>
Subject: query doesn't always follow 'correct' path..
Date: 2013-02-18 09:43:59
Message-ID: CAFCtE1nDc3t-2eXAuHatanQREvYnHRkEH2oGxU-nmWLpDj=Lpw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hello,

This is more of a query optimalisation question.

I have the following query:
SELECT sm.TICK_SERVER_ID
, sm.ITEMSEAT_ID AS ITEMSEAT_ID_master
, coalesce(sm.ITEMSEAT_STATUS, -999) AS ITEMSEAT_STATUS_master
, zm.ITEM_ID AS ITEM_ID_master
, coalesce(sm.ITEMSEAT_POS_X, -999) AS ITEMSEAT_POS_X
, coalesce(sm.ITEMSEAT_POS_Y, -999) AS ITEMSEAT_POS_Y
, sc.ITEMSEAT_ID AS ITEMSEAT_ID_child
, coalesce(sc.ITEMSEAT_STATUS, -999) AS ITEMSEAT_STATUS_child
, zc.ITEM_ID AS ITEM_ID_child
FROM oltp.ST_ABOITEM am
INNER JOIN oltp.ST_ITEMZONE zm ON zm.TICK_SERVER_ID = am.TICK_SERVER_ID AND
zm.ITEM_ID = am.MASTER_ITEM_ID
INNER JOIN oltp.ST_ITEMZONE zc ON zc.TICK_SERVER_ID = am.TICK_SERVER_ID AND
zc.ITEM_ID = am.CHILD_ITEM_ID
INNER JOIN oltp.ST_ITEMSEAT sm ON sm.TICK_SERVER_ID = zm.TICK_SERVER_ID
AND sm.ITEMZONE_ID = zm.ITEMZONE_ID
INNER JOIN oltp.ST_ITEMSEAT sc ON sc.TICK_SERVER_ID = zc.TICK_SERVER_ID
AND sc.ITEMZONE_ID = zc.ITEMZONE_ID AND sc.ITEMSEAT_POS_X =
sm.ITEMSEAT_POS_X AND sc.ITEMSEAT_POS_Y = sm.ITEMSEAT_POS_Y
WHERE am.TICK_SERVER_ID = 45
AND (
sc.ETL_RUN_ID = 1591
or am.ETL_RUN_ID = 1591
or sm.ETL_RUN_ID = 1591
);

When I don't touch the indexscan setting I get the following output:
Total query runtime: 611484 ms.
20359 rows retrieved.
and the following plan: http://explain.depesz.com/s/sDy

However, when I put set enable_indexscan=off; in fron of the same query I
get the following output:
Total query runtime: 16281 ms.
20599 rows retrieved.
and the followign plan: http://explain.depesz.com/s/EpP

Does anyone has an idea what triggers this bad plan, and how I can fix it?

wkr,
Bert

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Frank Lanitz 2013-02-18 10:51:11 Re: query doesn't always follow 'correct' path..
Previous Message Ben Morrow 2013-02-17 22:26:35 Re: How to reject overlapping timespans?