Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.96)
	(envelope-from <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1vJZYn-00BXHh-2E
	for pgsql-general@arkaria.postgresql.org;
	Thu, 13 Nov 2025 15:48:32 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.96)
	(envelope-from <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1vJZYl-002Zdc-0Z
	for pgsql-general@arkaria.postgresql.org;
	Thu, 13 Nov 2025 15:48:31 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.96)
	(envelope-from <sl.lmysyibrgaydinrugmytomjmeazdamzugizdqxi.rgq675hoqvjei@georgiou.vip>)
	id 1vJZYk-002ZdU-1t
	for pgsql-general@lists.postgresql.org;
	Thu, 13 Nov 2025 15:48:30 +0000
Received: from mail-107165.simplelogin.co ([79.135.107.165])
	by makus.postgresql.org with smtp (Exim 4.96)
	(envelope-from <sl.lmysyibrgaydinrugmytomjmeazdamzugizdqxi.rgq675hoqvjei@georgiou.vip>)
	id 1vJZYf-006wiZ-1X
	for pgsql-general@lists.postgresql.org;
	Thu, 13 Nov 2025 15:48:29 +0000
ARC-Seal: i=1; a=rsa-sha256; d=simplelogin.co; s=arc-20230626; t=1763048903;
	cv=none; b=kEmQyKe0LUIlJD81RzCWKCPUhmjLljnNrfChBTaOSCcQrOXVUjM2SPLdpXNVcIyTwGEDOYKRMMrPlw3XK7lgV2V8wXwNz6X+/ZKRHuARagrsaAI7AODkONsrTqlwuVaKIepyD4hmf0FNsP31EgIsxIpNM4doLqJKqKZ1r+UBzS9zpiibFppMm5v5155tcXba/PnB6wgKHdbwwd312S2LKS1tAAzkiYBEwXVNXbX9ndvUW1Xnyz4QtydopnLYhwDze4OGeqVfvGo6qNLmDugi00AsNIjJ2WnYMfSiyly2j9Eihq5v7ABPd66B1qg5GDvE0gsBSdpGrf+bJtB4hhdIDw==
ARC-Message-Signature: i=1; a=rsa-sha256; d=simplelogin.co; s=arc-20230626;
	t=1763048903; c=relaxed/simple;
	bh=WqX0DVzdQw8Mz1NMaCwo1IBT8IeUnBFdstNtKWqp9OM=;
	h=Date:Subject:In-Reply-To:From:To:References; b=zobpk/b2b8idbm97YJBBPNy14FaDXiXK1gsjhUzblxCsnYi3OH3TRv4kutlG6gsZtiPmW18M6w/OaZnvc/09Yinm3xCubOlrHMXy+mFbhg5K+xBoIQCAsEmdlDHQDu2TBvS8j/uvVCGPVFBLYdLx1pqCgzaDQWWq0oKlS08Fmo2J+y8Be7TQ41giyYru2oY0H+NdYvwoM4kOJfigG9znuVfRLBAp+s4IE0sObbqIBQpUwaRLhPV2waLO9jGUFWx3TnCAm2/DExBOSk/U8WabASHs26YwuIx/VLAvK9BEcOtwYnoFYDGdBHcLbLEi2T7bMaNrJPLM4glCjEHegrQlhw==
ARC-Authentication-Results: i=1; mail.protonmail.ch
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=georgiou.vip;
	s=dkim; t=1763048903;
	h=from:from:reply-to:subject:subject:date:date:message-id:message-id:
	 to:to:cc:mime-version:mime-version:content-type:content-type:
	 content-transfer-encoding:content-transfer-encoding:
	 in-reply-to:in-reply-to:references:references;
	bh=2niyCNEnax25JGJnqRhSRYp9tyKK6XzSg4lE4burI8g=;
	b=ZtRh+Lat3HGii9AmsxZ2ibUmZy/2P3O9qF6+5TX6Eu8gpVNdeSaoO86Q4VRcDthqWL3RSN
	ucoY7Qyb9eUuhfZwx8WKt/d73/zqNhyD1vwHJRczmVqEKa5zm9gHQ9D0NVVFmywXtsAFAe
	VIdJGaseETWHP1HpYrYmo/iuKjKHsks=
Content-Type: multipart/alternative;
 boundary="------------1xVck65V3iWap1VvILb8wlKC"
Date: Thu, 13 Nov 2025 10:48:11 -0500
MIME-Version: 1.0
Subject: Re: Forcing Index usage
In-Reply-To: 
 <VI6PPFA4BECA43135879DAC4554E69C07AFA7CDA@VI6PPFA4BECA431.EURP251.PROD.OUTLOOK.COM>
Content-Transfer-Encoding: 7bit
From: pg254kl@georgiou.vip
To: "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Message-ID: <176304890361.6.17252604690747648845.1004643171@georgiou.vip>
References: 
 <VI6PPFA4BECA43135879DAC4554E69C07AFA7CDA@VI6PPFA4BECA431.EURP251.PROD.OUTLOOK.COM>
X-SimpleLogin-Type: Reply
X-SimpleLogin-EmailLog-ID: 1004643171
X-SimpleLogin-Want-Signing: yes
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/176304890361.6.17252604690747648845.1004643171%40georgiou.vip>
Precedence: bulk

This is a multi-part message in MIME format.
--------------1xVck65V3iWap1VvILb8wlKC
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable

Divide and conquer.=C2=A0 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=20
be a partial index.

On 11/13/25 8:35 AM, Zahir Lalani wrote:
>
> Hello all
>
> Have a very frustrating issue =E2=80=93 we are seeing the same results =
in our=20
> PG17 UAT and PG14 Live setups (we are in transition).
>
> (I can provide the query planner but not doing here in case its too=20
> much info)
>
> Here is the query in question which we have re-written to try and get=20
> better outcomes =E2=80=93 this is a type-ahead lookup and the test belo=
w=20
> responds to the first three letters =E2=80=9Ctes=E2=80=9D
>
> The CTE runs in about 1.5s and the code below runs in around 1.2s=20
> which is acceptable
>
> The problem is as soon as we add in the =E2=80=9Cparent_id=E2=80=9D joi=
n
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0 -- ****** AND js.parent_id =3D jt.id -- looks for stat=
us=20
> based on job type, 4 type def looks for job statuses
>
> According to the query planner, this reverts to a seq scan and the=20
> time goes up to 30s!
>
> There are individual indexes on the 3 fields for JS (ctypes) as well=20
> as a composite key specifically designed for this use case.
>
> However, nothing we do seems to force it to use the indexes, this line=20
> always goes down the sequential scan route.
>
> Any suggestions would be welcome. If the planner will help, I can=20
> provide both for the mode with and without the line in question. Thank =
you
>
> with search as (
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0 select j.id, j.fk_job_type, j.fk_status, j.job_number,=
=20
> j.creative_name
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0 from jobs as j
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0 where (j.search_tsv @@ (to_tsquery('tes'||':*')))
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND j.fk_job_context_type =3D 1 -=
- jobs
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND (j.is_template IS FALSE)
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND j.is_deleted IS FALSE
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND j.fk_parent_id IS NULL -- Exc=
lude=20
> the sub jobs
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND j.is_encrypted IS FALSE
>
> =C2=A0=C2=A0=C2=A0 AND (j.fk_owning_agency_org =3D=20
> 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}')=20
> OR j.fk_agency_org =3D=20
> 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}'))=20
>
>
> )
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 SELECT
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 j.id=
 AS seq_id,
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 j.jo=
b_number AS job_number,
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 j.cr=
eative_name AS creative_name,
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 camp=
aign.id,
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 camp=
aign.plan_number,
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 camp=
aign.name as campaign_name
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 FROM search as j
>
> =C2=A0 INNER JOIN "public".relationship_module AS planning_job_relation=
 ON
>
> planning_job_relation.fk_child_id=3D j.id -- the campaign/job relations=
hip
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND =
planning_job_relation.fk_child_entity_id =3D=C2=A0 2 -- jobs
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND =
planning_job_relation.fk_parent_entity_id =3D 1 --=C2=A0 planning
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 INNER JOIN "public".planning=
 AS campaign ON
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 camp=
aign.id =3D planning_job_relation.fk_parent_id -- get=20
> the campaign details
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 INNER JOIN "public".c_=
types AS jt ON
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
 jt.local_id =3D j.fk_job_type
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
 AND jt.fk_type_def =3D 3 -- looks for job types
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0INNER JOIN "public".c_types AS js ON
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0 js.local_id =3D j.fk_status
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0 AND js.fk_type_def =3D 4
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0 -- ****** AND js.parent_id =3D jt.id-- looks for statu=
s=20
> based on job type, 4 type def looks for job statuses
>
> --=20
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0WHERE 1=3D1
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND =
js.object_key_area_id NOT IN (7, 8, 37) -- completed=20
> jobs =3D 7, cancelled jobs =3D 8, Client delivery confirmed jobs =3D 37=
.
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND =
campaign.fk_status NOT IN (1502, 1504, 1506) --=20
> completed planning =3D 1502, Cancelled planning =3D 1504, Client delive=
ry=20
> confirmed =3D 1506
>
> =C2=A0AND js.object_key_area_id NOT IN (7, 8, 37)
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0ORDER BY j.id desc
>
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 LIMIT 500;
>
> Z
>
--=20
regards,
Kiriakos Georgiou

--------------1xVck65V3iWap1VvILb8wlKC
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3DUTF=
-8">
  </head>
  <body>
    <p>Divide and conquer.=C2=A0 Get rid of the CTE temporarily.</p>
    <p>create temp table temp_search as &lt;insert search CTE query&gt;;<=
/p>
    <p>-- index temp_search *appropriately*</p>
    <p>analyze temp_search;</p>
    <p>Use it instead of the CTE.</p>
    <p>Remove the ORDER BY temporarily.</p>
    <p>Work on putting the right indices in place to make the above run
      fast.</p>
    <p>I assume you have a gin index on jobs(search_tsv), which perhaps
      should be a partial index.</p>
    <div class=3D"moz-cite-prefix">On 11/13/25 8:35 AM, Zahir Lalani
      wrote:<br>
    </div>
    <blockquote type=3D"cite"
cite=3D"mid:VI6PPFA4BECA43135879DAC4554E69C07AFA7CDA@VI6PPFA4BECA431.EURP=
251.PROD.OUTLOOK.COM">
      <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3DU=
TF-8">
      <meta name=3D"Generator"
        content=3D"Microsoft Word 15 (filtered medium)">
      <style>@font-face
	{font-family:Wingdings;
	panose-1:5 0 0 0 0 0 0 0 0 0;}@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face
	{font-family:Aptos;
	panose-1:2 11 0 4 2 2 2 2 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0cm;
	font-size:11.0pt;
	font-family:"Aptos",sans-serif;
	mso-ligatures:standardcontextual;
	mso-fareast-language:EN-US;}span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Aptos",sans-serif;
	color:windowtext;}.MsoChpDefault
	{mso-style-type:export-only;
	font-size:11.0pt;
	mso-fareast-language:EN-US;}div.WordSection1
	{page:WordSection1;}ol
	{margin-bottom:0cm;}ul
	{margin-bottom:0cm;}</style>
      <div class=3D"WordSection1">
        <p class=3D"MsoNormal">Hello all<o:p></o:p></p>
        <p class=3D"MsoNormal"><o:p>=C2=A0</o:p></p>
        <p class=3D"MsoNormal">Have a very frustrating issue =E2=80=93 we=
 are
          seeing the same results in our PG17 UAT and PG14 Live setups
          (we are in transition).<o:p></o:p></p>
        <p class=3D"MsoNormal"><o:p>=C2=A0</o:p></p>
        <p class=3D"MsoNormal">(I can provide the query planner but not
          doing here in case its too much info)<o:p></o:p></p>
        <p class=3D"MsoNormal"><o:p>=C2=A0</o:p></p>
        <p class=3D"MsoNormal">Here is the query in question which we hav=
e
          re-written to try and get better outcomes =E2=80=93 this is a
          type-ahead lookup and the test below responds to the first
          three letters =E2=80=9Ctes=E2=80=9D<o:p></o:p></p>
        <p class=3D"MsoNormal">The CTE runs in about 1.5s and the code
          below runs in around 1.2s which is acceptable<o:p></o:p></p>
        <p class=3D"MsoNormal">The problem is as soon as we add in the
          =E2=80=9Cparent_id=E2=80=9D join<o:p></o:p></p>
        <p class=3D"MsoNormal"><o:p>=C2=A0</o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 -- ****** AND js.parent_=
id
          =3D jt.id -- looks for status based on job type, 4 type def
          looks for job statuses<o:p></o:p></p>
        <p class=3D"MsoNormal"><o:p>=C2=A0</o:p></p>
        <p class=3D"MsoNormal">According to the query planner, this
          reverts to a seq scan and the time goes up to 30s!<o:p></o:p></=
p>
        <p class=3D"MsoNormal">There are individual indexes on the 3
          fields for JS (ctypes) as well as a composite key specifically
          designed for this use case.<o:p></o:p></p>
        <p class=3D"MsoNormal"><o:p>=C2=A0</o:p></p>
        <p class=3D"MsoNormal">However, nothing we do seems to force it t=
o
          use the indexes, this line always goes down the sequential
          scan route.<o:p></o:p></p>
        <p class=3D"MsoNormal"><o:p>=C2=A0</o:p></p>
        <p class=3D"MsoNormal">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<o:p></o:p></p>
        <p class=3D"MsoNormal"><o:p>=C2=A0</o:p></p>
        <p class=3D"MsoNormal">with search as (<o:p></o:p></p>
        <p class=3D"MsoNormal"><o:p>=C2=A0</o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 select j.id, j.fk_job_ty=
pe,
          j.fk_status, j.job_number, j.creative_name<o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 from jobs as j<o:p></o:p=
></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 where (j.search_tsv @@
          (to_tsquery('tes'||':*')))<o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND
          j.fk_job_context_type =3D 1 -- jobs<o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND
          (j.is_template IS FALSE)<o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND
          j.is_deleted IS FALSE<o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND
          j.fk_parent_id IS NULL -- Exclude the sub jobs<o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND
          j.is_encrypted IS FALSE<o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0 AND (j.fk_owning_agency=
_org =3D
ANY('{11126,87326,11129,11131,11144,11134,62158,9649,63095,52685,103238,5=
2449,56928,86885,52457,71727,40489,65669,36795,87213,51241,63980,63981,39=
903,60062,52456,68995,69010,60535,63979,65667,69002,40997,39475}')
          OR j.fk_agency_org =3D
ANY('{11126,87326,11129,11131,11144,11134,62158,9649,63095,52685,103238,5=
2449,56928,86885,52457,71727,40489,65669,36795,87213,51241,63980,63981,39=
903,60062,52456,68995,69010,60535,63979,65667,69002,40997,39475}'))=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0<o:p></o:p></p>
        <p class=3D"MsoNormal"><o:p>=C2=A0</o:p></p>
        <p class=3D"MsoNormal">)<o:p></o:p></p>
        <p class=3D"MsoNormal"><o:p>=C2=A0</o:p></p>
        <p class=3D"MsoNormal"><o:p>=C2=A0</o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
 SELECT<o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0 j.id AS seq_id,<o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0 j.job_number AS job_number,<o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0 j.creative_name AS
          creative_name,<o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0 campaign.id,<o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0 campaign.plan_number,<o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0 campaign.name as campaign_name<o:p></o:p></p>
        <p class=3D"MsoNormal"><o:p>=C2=A0</o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
 FROM search as j<o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0
          <o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0
          =C2=A0 INNER JOIN "public".relationship_module AS
          planning_job_relation ON<o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0
          planning_job_relation.fk_child_id=3D j.id -- the campaign/job
          relationship<o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0 AND
          planning_job_relation.fk_child_entity_id =3D=C2=A0 2 -- jobs<o:=
p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0 AND
          planning_job_relation.fk_parent_entity_id =3D 1 --=C2=A0 planni=
ng<o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0
          <o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0
          <o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
 INNER JOIN "public".planning AS
          campaign ON<o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0 campaign.id =3D
          planning_job_relation.fk_parent_id -- get the campaign details<=
o:p></o:p></p>
        <p class=3D"MsoNormal"><o:p>=C2=A0</o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0 INNER JOIN "public".c_types AS jt
          ON<o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 jt.local_id =3D j.fk_job_type<o:p></o:p></=
p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND jt.fk_type_def =3D 3 --
          looks for job types<o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0 <o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0<o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0INNER JOIN "public".c_types AS
          js ON<o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 js.local_id =3D j.=
fk_status<o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 AND js.fk_type_def =3D 4=
 <o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 -- ****** AND js.parent_=
id
          =3D jt.id-- looks for status based on job type, 4 type def look=
s
          for job statuses<o:p></o:p></p>
        <p class=3D"MsoNormal">-- <o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0WHERE 1=3D1<o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0 AND js.object_key_area_id NOT
          IN (7, 8, 37) -- completed jobs =3D 7, cancelled jobs =3D 8,
          Client delivery confirmed jobs =3D 37.<o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0 AND campaign.fk_status NOT IN
          (1502, 1504, 1506) -- completed planning =3D 1502, Cancelled
          planning =3D 1504, Client delivery confirmed =3D 1506=C2=A0=C2=A0
          <o:p></o:p></p>
        <p class=3D"MsoNormal" style=3D"text-indent:36.0pt">=C2=A0AND
          js.object_key_area_id NOT IN (7, 8, 37)<o:p></o:p></p>
        <p class=3D"MsoNormal"><o:p>=C2=A0</o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0 <o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0ORDER BY j.id desc<o:p></o:p></p>
        <p class=3D"MsoNormal">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
 LIMIT 500;<o:p></o:p></p>
        <p class=3D"MsoNormal"><o:p>=C2=A0</o:p></p>
        <p class=3D"MsoNormal">Z<o:p></o:p></p>
      </div>
    </blockquote>
    <pre class=3D"moz-signature" cols=3D"72">--=20
regards,
Kiriakos Georgiou</pre>
  </body>
</html>

--------------1xVck65V3iWap1VvILb8wlKC--