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
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? |