Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jWmEJ-0004PQ-OR for pgsql-performance@arkaria.postgresql.org; Thu, 07 May 2020 19:30:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jWmEI-0001Ta-3I for pgsql-performance@arkaria.postgresql.org; Thu, 07 May 2020 19:30:46 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jWmEH-0001TL-MS for pgsql-performance@lists.postgresql.org; Thu, 07 May 2020 19:30:45 +0000 Received: from sonic302-22.consmr.mail.ne1.yahoo.com ([66.163.186.148]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jWmED-0004c2-Jc for pgsql-performance@lists.postgresql.org; Thu, 07 May 2020 19:30:45 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1588879838; bh=3Li34CDYE8A1u0pCX2olXvVzMicatwZ1veO6MmSfvxA=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject; b=XOXG226ZDZz+JtSOKBtQ2cnBDLBLxPSp+elrLvdfSVj1m/3EpTqVBog3wBDGx8lZMLWvt0mbQm4CCYRa5X5jO5ky73BF/Xb+ujCe2yjA/LzQAfbfhbLqRCIKb2q2xxCRKPY2Cwr4zYR9KcRRuwGVGgYH4DExB0GsMNOGttslF69qR9HXCeDwCyQR0YNYvmkaXZYFpe1JTDvnnMnuxnRZ0gk48sD57QMSzDNSp/n0wFVOHePdXudPzVwMH4kiwd7fAa42Tzp1krzVn3rW82xws2lmWzUPV3BAkl7zMeK0vP598LbcqGRDelDh/Cx3rkXAw5+11GzG5HVZ0EH0xPzbBw== X-YMail-OSG: DDxhcJAVM1ldTDeIQubbMqR2rMIWaNcVKyDnr.ckaYC0Tgcps0MwPPzsN5ODxLe .VVkrAor9xnsMlnxVjSeHQMPkdGaM1YERdxgkeobJ6OkmcNLFckjD9yTRz_pMaUsckrnYd5OtEE2 N_acVgp0GAYph2DrzPNiqzANic4MImonjOh4gLMIlpIHn0EzCBPd1awweq25__ivgxHLTiFmqTYD JQ2xwUfa1OmHZ3q0BCI_weZXxFfkPHobwEjJIMz702EeVDjnvIiIen1XHG74BhNS_dUw6Z0GG8ZE 4SNWmRxdWDhTk4hh3kadef8zNawEdXetfm6.TueGUy1jCFKdCe0g75fy1GdqTxpeKW6c9_ZheUC5 4cfoyxGZUOtTh4AJwLNmNVWzJtHXBiYkYlZ5AwdncKPUPqqrocNVDKZOBT10YzogGEkLMjtTqiKy 8W4zfvlnuWLVvYpzlV_NxXfBcDLvzbVOI_.qDC_PppW8aXLfQo5xm9G0gqjg3DkoyR2mtZOs_v3C F1e6mPWbjGKDUn0l2TeqA06McIOTUYQf..AhuOXjgATTcCed5Xp0JRV4Qg0awaAgUHtS6UeUyXwI Wf2g4YTY2ybLDSETeH7LBCsSv08zM3EXDKbsnBFG9uhpbGd59usFFlF2eGrKrTYfnYWywTxKWunS k2ECPVSVaxyay.OuUP4flPqz5xGng9KWCaL6a7Tmg0gZaN7ZxIDRPHiQo8gkM5RXp5YkOflUb6pY uDEJk8VX6wCeub0yJxr1euYdJCXvZCeJQrG3LIHU9IOZTeRPyEbESft17c2fKx3U4UFozuBQxarZ .0N7It7UpUDUSfWNubhXyckql6Qu7tCK14mx7EF8dxcb6uWDxYSRzEpdd_mdhiu8A_yLM4sdyFq8 NSvSWRYB_xXIFJ.9OlCR0ZXhJpKSaUehId88VLkR5tDoGdE_OEvAapcP67sUAbf5fOMVVl.UVgum Wfze.WKbGeYdHi0gAHsNxv7LFFNdyKS3B64FPuxEVuTOBiu.CkqqTEN_y2.LFg_EUPntugMKxuYG 5JGg30yC_YeYf1IM_9slXX.kbok31KWGIcO5W0_fktLAAnEvGc_42cpz9i2_HA53sB62Q_HLehsr srgi6PYzsDMZZlWcYvpbwgc4VeGW9WG_oZFzNhytRCdHEdSVgde5CHtlIV.KC2R4TSmtCB_2G__w alzvwFIA6d2S.wI2BD68DKnvO1sK1TmQtLULbZVbytMjh00BXR6c5IAhOzjrDqiobBBCVciReFdI .EqC9oCydolSJftDLM8u0Hv0ourP3bFWpV8DapeLClSLOCwl0ztntE4DNnbisRbDcbFDTJdcRfOL 7KVraJebVhjNsjRa7OjNCj6bks3MBelPb0sbUMJPJ0yMDpA-- Received: from sonic.gate.mail.ne1.yahoo.com by sonic302.consmr.mail.ne1.yahoo.com with HTTP; Thu, 7 May 2020 19:30:38 +0000 Date: Thu, 7 May 2020 19:30:17 +0000 (UTC) From: Virendra Kumar Reply-To: Virendra Kumar To: Amarendra Konda Cc: pgsql-performance@lists.postgresql.org, pgsql-general Message-ID: <1656569367.3000113.1588879817805@mail.yahoo.com> In-Reply-To: References: <92fe033c-0221-30e3-1143-de8b0c8d923b@aklaver.com> <1604385414.2955858.1588876303535@mail.yahoo.com> Subject: Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3000112_1139130819.1588879817797" X-Mailer: WebService/1.1.15756 YMailNorrin Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:68.0) Gecko/20100101 Firefox/68.0 Content-Length: 49745 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_3000112_1139130819.1588879817797 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Here is my thought on why row is not limiting when joined vs why it is limi= ting when not joined. When not joined and where clause is having IN, it is using index process_ac= tivity_process_instance_id_app_id_created_idx which has columns process_ins= tance_id, created which is in order by and hence no additional ordering is = required and a direct rows limit can be applied here. When in join condition it has to fetch rows according to filter clause, joi= n them and then order ( sort node in plan) hence it cannot limit rows while= fetching it first time from the table. You are also missing pi.user_id =3D '317079413683604' in exists clause. It = is worth trying to put there and run explain again and see where it takes. = But to your point row limitation cannot happen in case of join as such in t= he query. Regards, Virendra=20 On Thursday, May 7, 2020, 11:52:00 AM PDT, Amarendra Konda wrote: =20 =20 Hi Virendra, Thanks for your time.=C2=A0 Here is the table and index structure =C2=A0process_activity =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 Table "public.process_activity" =C2=A0 =C2=A0 =C2=A0 =C2=A0Column =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0Type =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | = =C2=A0 =C2=A0 =C2=A0 =C2=A0 Modifiers =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 --------------------+-----------------------------+------------------------= ---- =C2=A0process_activity_id =C2=A0 =C2=A0 =C2=A0 =C2=A0 | bigint =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| not nul= l default next_id() =C2=A0process_activity_type =C2=A0 =C2=A0 =C2=A0 | smallint =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| not null =C2=A0voice_url =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| text =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=20 =C2=A0process_activity_user_id =C2=A0 =C2=A0| bigint =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| not null =C2=A0app_id =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | bigint =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| not nul= l =C2=A0process_instance_id =C2=A0 =C2=A0| bigint =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| not null =C2=A0alias =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| text =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0| not null =C2=A0read_by_user =C2=A0 =C2=A0 =C2=A0 | smallint =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| default 0 =C2=A0source =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | smallint =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| default 0 =C2=A0label_category_id =C2=A0| bigint =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=20 =C2=A0label_id =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | bigint =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=20 =C2=A0csat_response_id =C2=A0 | bigint =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=20 =C2=A0process_activity_fragments =C2=A0| jsonb =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=20 =C2=A0created =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| timestamp without = time zone | not null =C2=A0updated =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| timestamp without = time zone |=20 =C2=A0rule_id =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| bigint =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=20 =C2=A0marketing_reply_id | bigint =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=20 =C2=A0delivered_at =C2=A0 =C2=A0 =C2=A0 | timestamp without time zone |=20 =C2=A0reply_fragments =C2=A0 =C2=A0| jsonb =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=20 =C2=A0status_fragment =C2=A0 =C2=A0| jsonb =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=20 =C2=A0internal_meta =C2=A0 =C2=A0 =C2=A0| jsonb =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=20 =C2=A0interaction_id =C2=A0 =C2=A0 | text =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=20 =C2=A0do_not_translate =C2=A0 | boolean =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=20 =C2=A0should_translate =C2=A0 | integer =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=20 =C2=A0in_reply_to =C2=A0 =C2=A0 =C2=A0 =C2=A0| jsonb =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=20 Indexes: =C2=A0 =C2=A0 "process_activity_pkey" PRIMARY KEY, btree (process_activity_= id) =C2=A0 =C2=A0 "fki_process_activity_konotor_user_user_id" btree (process_ac= tivity_user_id) WITH (fillfactor=3D'70') =C2=A0 =C2=A0 "process_activity_process_instance_id_app_id_created_idx" btr= ee (process_instance_id, app_id, created) WITH (fillfactor=3D'70') =C2=A0 =C2=A0 "process_activity_process_instance_id_app_id_read_by_user_cre= ated_idx" btree (process_instance_id, app_id, read_by_user, created) WITH (= fillfactor=3D'70') =C2=A0 =C2=A0 "process_activity_process_instance_id_idx" btree (process_ins= tance_id) WITH (fillfactor=3D'70') =C2=A0 process_instance =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Table "public.process_instance" =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Column =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Type =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Modifiers =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 -------------------------+-----------------------------+-------------------= ---------- =C2=A0process_instance_id =C2=A0 =C2=A0 | bigint =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| not null default next= _id() =C2=A0process_instance_alias =C2=A0| text =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| not null =C2=A0app_id =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= | bigint =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0| not null =C2=A0user_id =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | big= int =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0| not null =C2=A0 Indexes: =C2=A0 =C2=A0 "process_instance_pkey" PRIMARY KEY, btree (process_instance_= id) =C2=A0 =C2=A0 "fki_conv_konotor_user_user_id" btree (user_id) WITH (fillfac= tor=3D'70') Regards, Amarendra On Fri, May 8, 2020 at 12:01 AM Virendra Kumar wrote: Sending table structure with indexes might help little further in understan= ding. Regards, Virendra On Thursday, May 7, 2020, 11:08:14 AM PDT, Amarendra Konda wrote: =20 =20 Hi David, In earlier=C2=A0reply, Over looked=C2=A0another condition, hence please ign= ore that one Here is the correct one with all the needed conditions. According to the la= test one, exists also not limiting rows from the process_activity table. EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) =C2=A0SELECT pa.process_activity= _id AS pa_process_activity_id =C2=A0FROM process_activity pa WHERE pa.app_i= d =3D '126502930200650' AND pa.created > '1970-01-01 00:00:00' =C2=A0AND EX= ISTS ( SELECT 1 FROM process_instance pi where pi.app_id =3D pa.app_id AND = pi.process_instance_id =3D pa.process_instance_id =C2=A0AND pi.user_id =3D = '137074931866340') ORDER BY pa.process_instance_id, =C2=A0pa.created limit = 50; =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=20 =C2=A0 =C2=A0 =C2=A0 QUERY PLAN =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------- ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------- ----------------------- =C2=A0Limit =C2=A0(cost=3D1079.44..1079.52 rows=3D32 width=3D24) (actual ti= me=3D85.747..85.777 rows=3D50 loops=3D1) =C2=A0 =C2=A0Output: pa.process_activity_id, pa.process_instance_id, pa.cre= ated =C2=A0 =C2=A0Buffers: shared hit=3D43070 =C2=A0 =C2=A0-> =C2=A0Sort =C2=A0(cost=3D1079.44..1079.52 rows=3D32 width= =3D24) (actual time=3D85.745..85.759 rows=3D50 loops=3D1) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Output: pa.process_activity_id, pa.proces= s_instance_id, pa.created =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Sort Key: pa.process_instance_id, pa.crea= ted =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Sort Method: top-N heapsort =C2=A0Memory:= 28kB =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D43070 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Nested Loop =C2=A0(cost=3D1.14..= 1078.64 rows=3D32 width=3D24) (actual time=3D0.025..72.115 rows=3D47011 loo= ps=3D1) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Output: pa.process_a= ctivity_id, pa.process_instance_id, pa.created =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit= =3D43070 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan = using fki_conv_konotor_user_user_id on public.process_instance pi =C2=A0(co= st=3D0.43..2.66 rows=3D1 width=3D16) (actual time=3D0.010..0.015 rows=3D2 l= oops=3D1) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0Output: pi.app_id, pi.process_instance_id =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0Index Cond: (c.user_id =3D '137074931866340'::bigint) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0Filter: (c.app_id =3D '126502930200650'::bigint) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0Buffers: shared hit=3D5 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan = using process_activity_process_instance_id_app_id_created_idx on public.pro= cess_activity pa =C2=A0(cost=3D0.70..1061.62 rows=3D1436 width=3D32) (actua= l time=3D0.011..20.320 rows=3D23506 loops=3D2) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0Output: pa.process_activity_id, pa.process_activity_type, pa.voice_url, = pa.process_activity_user_id, pa.app_id, pa.process_instance_id, pa.alias, p= a.read_by_user, pa.source, pa.label_category_id, pa.label_id, pa.csat_respo= ns e_id, pa.process_activity_fragments, pa.created, pa.updated, pa.rule_id, pa= .marketing_reply_id, pa.delivered_at, pa.reply_fragments, pa.status_fragmen= t, pa.internal_meta, pa.interaction_id, pa.do_not_translate, pa.should_tr anslate, pa.in_reply_to =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0Index Cond: ((m.process_instance_id =3D pi.process_instance_id) AND (m.a= pp_id =3D '126502930200650'::bigint) AND (m.created > '1970-01-01 00:00:00'= ::timestamp without time zone)) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0Buffers: shared hit=3D43065 =C2=A0Planning time: 0.455 ms =C2=A0Execution time: 85.830 ms On Thu, May 7, 2020 at 11:19 PM Amarendra Konda wro= te: Hi David, Thanks for the reply.This has optimized number of rows.=C2=A0 Can you please explain, why it is getting more columns in output,=C2=A0even= though we have asked for=C2=A0only=C2=A0one column ?=C2=A0 =C2=A0EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) =C2=A0SELECT pa.process_ac= tivity_id AS pa_process_activity_id =C2=A0FROM process_activity pa WHERE pa= .app_id =3D '126502930200650' AND pa.created > '1970-01-01 00:00:00' =C2=A0= AND EXISTS ( SELECT 1 FROM process_instance pi where pi.app_id =3D pa.app_i= d =C2=A0AND pi.user_id =3D '137074931866340') ORDER BY pa.process_instance_= id,m.created limit 50; =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=20 =C2=A0 =C2=A0QUERY PLAN =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0=20 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------- ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------- ----------------- =C2=A0Limit =C2=A0(cost=3D1.14..37.39 rows=3D50 width=3D24) (actual time=3D= 821.283..891.629 rows=3D50 loops=3D1) =C2=A0 =C2=A0Output: pa.process_activity_id, pa.process_instance_id, pa.cre= ated =C2=A0 =C2=A0Buffers: shared hit=3D274950 =C2=A0 =C2=A0-> =C2=A0Nested Loop Semi Join =C2=A0(cost=3D1.14..266660108.7= 8 rows=3D367790473 width=3D24) (actual time=3D821.282..891.607 rows=3D50 lo= ops=3D1) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Output: pa.process_activity_id, pa.proces= s_instance_id, pa.created =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D274950 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan using process_activit= y_process_instance_id_app_id_created_idx on public.process_activity pa =C2= =A0(cost=3D0.70..262062725.21 rows=3D367790473 width=3D32) (actual time=3D8= 21.253..891.517 rows=3D50 loops=3D1) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Output: pa.process_a= ctivity_id, pa.process_activity_type, pa.voice_url, pa.process_activity_use= r_id, pa.app_id, pa.process_instance_id, pa.alias, pa.read_by_user, pa.sour= ce, pa.label_category_id, pa.label_id, pa.csat_response_id,=20 m.process_activity_fragments, pa.created, pa.updated, pa.rule_id, pa.market= ing_reply_id, pa.delivered_at, pa.reply_fragments, pa.status_fragment, pa.i= nternal_meta, pa.interaction_id, pa.do_not_translate, pa.should_translat e, pa.in_reply_to =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: ((m.app_= id =3D '126502930200650'::bigint) AND (m.created > '1970-01-01 00:00:00'::t= imestamp without time zone)) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit= =3D274946 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Materialize =C2=A0(cost=3D0.43..= 2.66 rows=3D1 width=3D8) (actual time=3D0.001..0.001 rows=3D1 loops=3D50) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Output: pi.app_id =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit= =3D4 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan = using fki_conv_konotor_user_user_id on public.process_instance pi =C2=A0(co= st=3D0.43..2.66 rows=3D1 width=3D8) (actual time=3D0.020..0.020 rows=3D1 lo= ops=3D1) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0Output: pi.app_id =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0Index Cond: (pi.user_id =3D '137074931866340'::bigint) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0Filter: (pi.app_id =3D '126502930200650'::bigint) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0Buffers: shared hit=3D4 =C2=A0Planning time: 0.297 ms =C2=A0Execution time: 891.686 ms (20 rows) On Thu, May 7, 2020 at 9:17 PM David G. Johnston wrote: On Thu, May 7, 2020 at 7:40 AM Adrian Klaver wr= ote: On 5/7/20 4:19 AM, Amarendra Konda wrote: > Hi, >=20 > PostgreSQL version :=C2=A0PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compil= ed=20 > by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit >=20 > We have noticed huge difference interms of execution plan ( response=20 > time) , When we pass the direct values=C2=A0 Vs=C2=A0 inner query to IN c= lause. >=20 > High level details of the use case are as follows >=20 >=C2=A0 =C2=A0* As part of the SQL there are 2 tables named Process_instanc= e >=C2=A0 =C2=A0 =C2=A0(master) and Process_activity ( child) >=C2=A0 =C2=A0* Wanted to fetch TOP 50 rows from=C2=A0 Process_activity tab= le for the >=C2=A0 =C2=A0 =C2=A0given values of the Process_instance. >=C2=A0 =C2=A0* When we used Inner Join / Inner query ( query1)=C2=A0 betwe= en parent >=C2=A0 =C2=A0 =C2=A0table and child table , LIMIT is not really taking in = to account. >=C2=A0 =C2=A0 =C2=A0Instead it is fetching more rows and columns that requ= ired, and >=C2=A0 =C2=A0 =C2=A0finally limiting the result It is doing what you told it to do which is SELECT all=20 process_instance_i's for user_id=3D'317079413683604' and app_id =3D=20 '427380312000560' and then filtering further. I am going to guess that=20 if you run the inner query alone you will find it returns ~23496 rows. You might have better results if you an actual join between=20 process_activity and process_instance. Something like below(obviously=20 not tested): What the OP seems to want is a semi-join: (not tested) SELECT pa.process_activity_id =C2=A0FROM process_activity pa WHERE pa.app_i= d =3D '427380312000560' AND pa.created > '1970-01-01 00:00:00'AND EXISTS (= =C2=A0 SELECT 1 FROM process_instance pi WHERE pi.app_id =3D pa.app_id AND = pi.user_id =3D '317079413683604') ORDER BY=C2=A0pa.process_instance_id,pa.created limit 50; I'm unsure exactly how this will impact the plan choice but it should be an= improvement, and in any case more correctly defines what it is you are loo= king for. David J. =20 =20 ------=_Part_3000112_1139130819.1588879817797 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
=
Here is my thought on why row i= s not limiting when joined vs why it is limiting when not joined.

When not joined and where clause is having IN, it is using index= process_activity_process_instance_id_app_id_created_idx which has co= lumns process_instance_id, created which is in order by and hence no = additional ordering is required and a direct rows limit can be applied here= .
Whe= n in join condition it has to fetch rows according to filter clause, join t= hem and then order ( sort node in plan) hence it cannot limit rows while fe= tching it first time from the table.

You are also missing pi.user_id =3D '= 317079413683604' in exists clause. It is worth trying to put there and run = explain again and see where it takes. But to your point row limitation cann= ot happen in case of join as such in the query.

Regards,
Virendra<= /div>


=20
=20
On Thursday, May 7, 2020, 11:52:00 AM PDT, Amarendra Ko= nda <amar.vijaya@gmail.com> wrote:


Hi Virendra,

Thanks for your time.&n= bsp;

Here is the table and index st= ructure

 process_activit= y
             = ;               Table "public.process_ac= tivity"
       Column     &= nbsp; |            Type       =       |         Modifiers     =      
--------------------+---------------= --------------+----------------------------
 process= _activity_id         | bigint       &nbs= p;              | not null default next_= id()
 process_activity_type       | s= mallint                   &nbs= p;| not null
 voice_url         =  | text                  =      |
 process_activity_user_id &n= bsp;  | bigint                =      | not null
 app_id    = ;         | bigint           &= nbsp;          | not null
 = process_instance_id    | bigint          = ;            | not null
&nb= sp;alias              | text   &nbs= p;                    | n= ot null
 read_by_user       | smallin= t                    | de= fault 0
 source           &= nbsp; | smallint                 &n= bsp;  | default 0
 label_category_id  | bi= gint                     =  |
 label_id          = ; | bigint                   &= nbsp;  |
 csat_response_id   | bigint &nb= sp;                    | =
 process_activity_fragments  | jsonb   &n= bsp;                   |
 created            | time= stamp without time zone | not null
 updated   &= nbsp;        | timestamp without time zone |
 rule_id            | bigint &= nbsp;                    = |
 marketing_reply_id | bigint      =                |
 delivered_at       | timestamp without time zone | <= br clear=3D"none"> reply_fragments    | jsonb     =                   |
 status_fragment    | jsonb       &= nbsp;               |
 internal_meta      | jsonb        = ;               |
&nb= sp;interaction_id     | text           &= nbsp;            |
 d= o_not_translate   | boolean             =         |
 should_translate &nb= sp; | integer                  = ;   |
 in_reply_to        = | jsonb                   &nbs= p;   |
Indexes:
    "pr= ocess_activity_pkey" PRIMARY KEY, btree (process_activity_id)
    "fki_process_activity_konotor_user_user_id" btree (proce= ss_activity_user_id) WITH (fillfactor=3D'70')
  &nbs= p; "process_activity_process_instance_id_app_id_created_idx" btree (= process_instance_id, app_id, created) WITH (fillfactor=3D'70')
    "process_activity_process_instance_id_app_id_read_by_us= er_created_idx" btree (process_instance_id, app_id, read_by_user, created) = WITH (fillfactor=3D'70')
    "process_activity_= process_instance_id_idx" btree (process_instance_id) WITH (fillfactor=3D'70= ')
 



= process_instance
        &= nbsp;                    = Table "public.process_instance"
      &nbs= p;  Column          |       &n= bsp;    Type             |   &= nbsp;      Modifiers          
-------------------------+-----------------------------+-----= ------------------------
 process_instance_id  =   | bigint                 &n= bsp;    | not null default next_id()
 proc= ess_instance_alias  | text             &= nbsp;          | not null
 = app_id                  | bigi= nt                     &n= bsp;| not null
 user_id         =         | bigint           &nb= sp;          | not null
 Indexes:
    "process_instance= _pkey" PRIMARY KEY, btree (process_instance_id)
  &n= bsp; "fki_conv_konotor_user_user_id" btree (user_id) WITH (fillfacto= r=3D'70')

Regard= s, Amarendra

On Fri, May 8, 2020 at 12:01 AM Virendra Kumar <= viru_7683@yahoo.com> wrote:
=
Sending table structure with indexes might help littl= e further in understanding.

Regards,
Virendra

=20
=20
On Thursday, May 7, 2020, 11:08:14 AM PDT, Amarendra Ko= nda <amar.vijaya@gmail.com> wrote:


Hi David,

In earlier reply, Over looked another co= ndition, hence please ignore that one

Here is the correct one with all the needed conditions. According to the= latest one, exists also not limiting rows from the process_activity table.=


EXPL= AIN (ANALYZE, COSTS, VERBOSE, BUFFERS)  SELECT pa.process_activity_id = AS pa_process_activity_id  FROM process_activity pa WHERE pa.app_id = =3D '126502930200650' AND pa.created > '1970-01-01 00:00:00'  AND E= XISTS ( SELECT 1 FROM process_instance pi where pi.app_id =3D pa.app_id AND= pi.process_instance_id =3D pa.process_instance_id  AND pi.user= _id =3D '137074931866340') ORDER BY pa.process_instance_id,  pa.create= d limit 50;
            &nb= sp;                     &= nbsp;                    =                     &nbs= p;                     &n= bsp;                     =                      = ;                     &nb= sp;                     &= nbsp;                
      QUERY PLAN           &n= bsp;                     =                      = ;                     &nb= sp;                     &= nbsp;                    =                     &nbs= p;                     &n= bsp;                     =    
            &= nbsp;          
----------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------
--------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------------------
-----------------------<= br clear=3D"none"> Limit  (cost=3D1079.44..1079.52 rows=3D32 widt= h=3D24) (actual time=3D85.747..85.777 rows=3D50 loops=3D1)
   Output: pa.process_activity_id, pa.process_instance_id, pa.c= reated
   Buffers: shared hit=3D43070
   ->  Sort  (cost=3D1079.44..1079.52 rows= =3D32 width=3D24) (actual time=3D85.745..85.759 rows=3D50 loops=3D1)
         Output: pa.process_activity_i= d, pa.process_instance_id, pa.created
     = ;    Sort Key: pa.process_instance_id, pa.created
         Sort Method: top-N heapsort  Memo= ry: 28kB
         Buffers: share= d hit=3D43070
         -> &nb= sp;Nested Loop  (cost=3D1.14..1078.64 rows=3D32 width=3D24) (actual ti= me=3D0.025..72.115 rows=3D47011 loops=3D1)
    =            Output: pa.process_activity_id, pa= .process_instance_id, pa.created
      &nb= sp;        Buffers: shared hit=3D43070
               ->  Index = Scan using fki_conv_konotor_user_user_id on public.process_instance pi &nbs= p;(cost=3D0.43..2.66 rows=3D1 width=3D16) (actual time=3D0.010..0.015 rows= =3D2 loops=3D1)
           =          Output: pi.app_id, pi.process_instance_i= d
               =      Index Cond: (c.user_id =3D '137074931866340'::bigint)<= br clear=3D"none">                &= nbsp;    Filter: (c.app_id =3D '126502930200650'::bigint)
                  &= nbsp;  Buffers: shared hit=3D5
      =          ->  Index Scan using process_acti= vity_process_instance_id_app_id_created_idx on public.process_activity pa &= nbsp;(cost=3D0.70..1061.62 rows=3D1436 width=3D32) (actual time=3D0.011.= .20.320 rows=3D23506 loops=3D2)
      =                Output: pa.process_a= ctivity_id, pa.process_activity_type, pa.voice_url, pa.process_activity_use= r_id, pa.app_id, pa.process_instance_id, pa.alias, pa.read_by_user, pa.sour= ce, pa.label_category_id, pa.label_id, pa.csat_respons
e_= id, pa.process_activity_fragments, pa.created, pa.updated, pa.rule_id, pa.m= arketing_reply_id, pa.delivered_at, pa.reply_fragments, pa.status_fragment,= pa.internal_meta, pa.interaction_id, pa.do_not_translate, pa.should_tr
anslate, pa.in_reply_to
    &nbs= p;                Index Cond: ((m.p= rocess_instance_id =3D pi.process_instance_id) AND (m.app_id =3D '126502930= 200650'::bigint) AND (m.created > '1970-01-01 00:00:00'::timestamp witho= ut time zone))
            =          Buffers: shared hit=3D43065
 Planning time: 0.455 ms
 Execution time: = 85.830 ms

On Thu, May 7, 2020 at 11:19 PM Amarendra Konda = <amar.vijaya@gmail.com> wrote:
=
Hi David,

Thanks for the reply.This has optimized number of rows= . 

Can you please explain, why= it is getting more columns in output, even though we have asked for&n= bsp;only one column ? 


 EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) &nbs= p;SELECT pa.process_activity_id AS pa_process_activity_id  FROM proces= s_activity pa WHERE pa.app_id =3D '126502930200650' AND pa.created > '19= 70-01-01 00:00:00'  AND EXISTS ( SELECT 1 FROM process_instance pi whe= re pi.app_id =3D pa.app_id  AND pi.user_id =3D '137074931866340') ORDE= R BY pa.process_instance_id,m.created limit 50;
  &n= bsp;                     =                      = ;                     &nb= sp;                     &= nbsp;                    =                     &nbs= p;                     &n= bsp;                     =                      = ;      
   QUERY PLAN   &n= bsp;                     =                      = ;                     &nb= sp;                     &= nbsp;                    =                     &nbs= p;                     &n= bsp;                     =              
  =                
-= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------
-----= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -----------------------------------------------
---------= --------
 Limit  (cost=3D1.14..37.39 rows=3D50 = width=3D24) (actual time=3D821.283..891.629 rows=3D50 loops=3D1)
   Output: pa.process_activity_id, pa.process_instance_= id, pa.created
   Buffers: shared hit=3D274950<= br clear=3D"none">   ->  Nested Loop Semi Join  (cos= t=3D1.14..266660108.78 rows=3D367790473 width=3D24) (actual time=3D821.282.= .891.607 rows=3D50 loops=3D1)
       =  Output: pa.process_activity_id, pa.process_instance_id, pa.created         Buffers: shared hit=3D27= 4950
         ->  Index = Scan using process_activity_process_instance_id_app_id_created_idx on publi= c.process_activity pa  (cost=3D0.70..262062725.21 rows=3D367790473 wid= th=3D32) (actual time=3D821.253..891.517 rows=3D50 loops=3D1)
               Output: pa.p= rocess_activity_id, pa.process_activity_type, pa.voice_url, pa.process_acti= vity_user_id, pa.app_id, pa.process_instance_id, pa.alias, pa.read_by_user,= pa.source, pa.label_category_id, pa.label_id, pa.csat_response_id,
m.process_activity_fragments, pa.created, pa.updated, pa.rule_i= d, pa.marketing_reply_id, pa.delivered_at, pa.reply_fragments, pa.status_fr= agment, pa.internal_meta, pa.interaction_id, pa.do_not_translate, pa.should= _translat
e, pa.in_reply_to
  =              Index Cond: ((m.app_id =3D = '126502930200650'::bigint) AND (m.created > '1970-01-01 00:00:00'::times= tamp without time zone))
        &nbs= p;      Buffers: shared hit=3D274946
 = ;        ->  Materialize  (cost=3D0.43..2.= 66 rows=3D1 width=3D8) (actual time=3D0.001..0.001 rows=3D1 loops=3D50)
               Outpu= t: pi.app_id
            &n= bsp;  Buffers: shared hit=3D4
      &= nbsp;        ->  Index Scan using fki_conv_kono= tor_user_user_id on public.process_instance pi  (cost=3D0.43..2.66 row= s=3D1 width=3D8) (actual time=3D0.020..0.020 rows=3D1 loops=3D1)
                  &n= bsp;  Output: pi.app_id
        =              Index Cond: (pi.user_id =3D= '137074931866340'::bigint)
        &= nbsp;            Filter: (pi.app_id =3D '1265= 02930200650'::bigint)
          =            Buffers: shared hit=3D4
 Planning time: 0.297 ms
 Execution t= ime: 891.686 ms
(20 rows)
<= /div>
On Thu, May 7, 2020 at 9:17 P= M David G. Johnston <david.g.johnston@gmail.com= > wrote:
On Thu, M= ay 7, 2020 at 7:40 AM Adrian Klaver <adrian.klaver@a= klaver.com> wrote:
On 5/7/20 4:19 AM, Amarendra Konda wrote:
> Hi,
>
> PostgreSQL version : PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, com= piled
> by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
>
> We have noticed huge difference interms of execution plan ( response <= br clear=3D"none"> > time) , When we pass the direct values  Vs  inner query to I= N clause.
>
> High level details of the use case are as follows
>
>   * As part of the SQL there are 2 tables named Process_inst= ance
>     (master) and Process_activity ( child)
>   * Wanted to fetch TOP 50 rows from  Process_activity = table for the
>     given values of the Process_instance.
>   * When we used Inner Join / Inner query ( query1)  be= tween parent
>     table and child table , LIMIT is not really taking = in to account.
>     Instead it is fetching more rows and columns that r= equired, and
>     finally limiting the result

It is doing what you told it to do which is SELECT all
process_instance_i's for user_id=3D'317079413683604' and app_id =3D
'427380312000560' and then filtering further. I am going to guess that
if you run the inner query alone you will find it returns ~23496 rows.
You might have better results if you an actual join between
process_activity and process_instance. Something like below(obviously
not tested):

What the OP seems to want is a semi-join= :

(not tested)

=
SELECT pa.process_activity_id  
FROM process_acti= vity pa WHERE pa.app_id =3D '427380312000560' AND pa.created > '1970-01-= 01 00:00:00'
AND EXISTS (
  SELECT 1 FROM process_instance pi WHERE pi.app_id= =3D pa.app_id AND pi.user_id =3D '317079413683604'
)
=
ORDER BY&nbs= p;
pa.process_instance_id,
pa.created limit 50;

<= /div>
I'm uns= ure exactly how this will impact the plan choice but it should be an improv= ement, and in any case more correctly defines what it is you are looking fo= r.

David J.

------=_Part_3000112_1139130819.1588879817797--