Postgres Pro
Downloads
Home   >   mailing lists

query doesn't always follow 'correct' path.. - Mailing list pgsql-sql

From Bert
Subject query doesn't always follow 'correct' path..
Date
Msg-id CAFCtE1nDc3t-2eXAuHatanQREvYnHRkEH2oGxU-nmWLpDj=Lpw@mail.gmail.com
Whole thread Raw
Responses Re: query doesn't always follow 'correct' path..
Re: query doesn't always follow 'correct' path..
List pgsql-sql
Tree view
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

pgsql-sql by date:

Previous
From: Ben Morrow
Date:
Subject: Re: How to reject overlapping timespans?
Next
From: Frank Lanitz
Date:
Subject: Re: query doesn't always follow 'correct' path..