Re: Forcing Index usage

Divide and conquer.  Get rid of the CTE temporarily.

create temp table temp_search as <insert search CTE query>;

-- index temp_search *appropriately*

analyze temp_search;

Use it instead of the CTE.

Remove the ORDER BY temporarily.

Work on putting the right indices in place to make the above run fast.

I assume you have a gin index on jobs(search_tsv), which perhaps should
be a partial index.

On 11/13/25 8:35 AM, Zahir Lalani wrote:
>
> 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
>
--
regards,
Kiriakos Georgiou