public inbox for [email protected]
help / color / mirror / Atom feedFrom: Zahir Lalani <[email protected]>
To: [email protected] <[email protected]>
Subject: Forcing Index usage
Date: Thu, 13 Nov 2025 13:35:36 +0000
Message-ID: <VI6PPFA4BECA43135879DAC4554E69C07AFA7CDA@VI6PPFA4BECA431.EURP251.PROD.OUTLOOK.COM> (raw)
Hello all
Have a very frustrating issue - we are seeing the same results in our PG17 UAT and PG14 Live setups (we are in transition).
(I can provide the query planner but not doing here in case its too much info)
Here is the query in question which we have re-written to try and get better outcomes - this is a type-ahead lookup and the test below responds to the first three letters "tes"
The CTE runs in about 1.5s and the code below runs in around 1.2s which is acceptable
The problem is as soon as we add in the "parent_id" join
-- ****** AND js.parent_id = jt.id -- looks for status based on job type, 4 type def looks for job statuses
According to the query planner, this reverts to a seq scan and the time goes up to 30s!
There are individual indexes on the 3 fields for JS (ctypes) as well as a composite key specifically designed for this use case.
However, nothing we do seems to force it to use the indexes, this line always goes down the sequential scan route.
Any suggestions would be welcome. If the planner will help, I can provide both for the mode with and without the line in question. Thank you
with search as (
select j.id, j.fk_job_type, j.fk_status, j.job_number, j.creative_name
from jobs as j
where (j.search_tsv @@ (to_tsquery('tes'||':*')))
AND j.fk_job_context_type = 1 -- jobs
AND (j.is_template IS FALSE)
AND j.is_deleted IS FALSE
AND j.fk_parent_id IS NULL -- Exclude the sub jobs
AND j.is_encrypted IS FALSE
AND (j.fk_owning_agency_org = ANY('{11126,87326,11129,11131,11144,11134,62158,9649,63095,52685,103238,52449,56928,86885,52457,71727,40489,65669,36795,87213,51241,63980,63981,39903,60062,52456,68995,69010,60535,63979,65667,69002,40997,39475}') OR j.fk_agency_org = ANY('{11126,87326,11129,11131,11144,11134,62158,9649,63095,52685,103238,52449,56928,86885,52457,71727,40489,65669,36795,87213,51241,63980,63981,39903,60062,52456,68995,69010,60535,63979,65667,69002,40997,39475}'))
)
SELECT
j.id AS seq_id,
j.job_number AS job_number,
j.creative_name AS creative_name,
campaign.id,
campaign.plan_number,
campaign.name as campaign_name
FROM search as j
INNER JOIN "public".relationship_module AS planning_job_relation ON
planning_job_relation.fk_child_id= j.id -- the campaign/job relationship
AND planning_job_relation.fk_child_entity_id = 2 -- jobs
AND planning_job_relation.fk_parent_entity_id = 1 -- planning
INNER JOIN "public".planning AS campaign ON
campaign.id = planning_job_relation.fk_parent_id -- get the campaign details
INNER JOIN "public".c_types AS jt ON
jt.local_id = j.fk_job_type
AND jt.fk_type_def = 3 -- looks for job types
INNER JOIN "public".c_types AS js ON
js.local_id = j.fk_status
AND js.fk_type_def = 4
-- ****** AND js.parent_id = jt.id-- looks for status based on job type, 4 type def looks for job statuses
--
WHERE 1=1
AND js.object_key_area_id NOT IN (7, 8, 37) -- completed jobs = 7, cancelled jobs = 8, Client delivery confirmed jobs = 37.
AND campaign.fk_status NOT IN (1502, 1504, 1506) -- completed planning = 1502, Cancelled planning = 1504, Client delivery confirmed = 1506
AND js.object_key_area_id NOT IN (7, 8, 37)
ORDER BY j.id desc
LIMIT 500;
Z
view thread (3+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Forcing Index usage
In-Reply-To: <VI6PPFA4BECA43135879DAC4554E69C07AFA7CDA@VI6PPFA4BECA431.EURP251.PROD.OUTLOOK.COM>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox