Обсуждение: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNERQuery )

On 5/7/20 4:19 AM, Amarendra Konda wrote:
> Hi,
>
> PostgreSQL version : PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled
> 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
> time) , When we pass the direct values  Vs  inner query to IN clause.
>
> High level details of the use case are as follows
>
>   * As part of the SQL there are 2 tables named Process_instance
>     (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)  between parent
>     table and child table , LIMIT is not really taking in to account.
>     Instead it is fetching more rows and columns that required, 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='317079413683604' and app_id =
'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):

SELECT
     pa.process_activity_id
FROM
     process_activity pa
JOIN
     process_instance pi
ON
     pa.process_instance_id = pi.process_instance_id
WHERE
     pa.app_id = '427380312000560'
     AND
          pa.created > '1970-01-01 00:00:00'
     AND
          pi.user_id = '317079413683604'
ORDER BY
     pa.process_instance_id,
     pa.created
LIMIT 50;

The second query is not equivalent as you are not filtering on user_id
and you are filtering on only three process_instance_id's.


>   *
>
>
> *Query1*
>
> web_1=> EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT
> pa.process_activity_id  FROM process_activity pa WHERE pa.app_id =
> '427380312000560' AND pa.created > '1970-01-01 00:00:00' AND
> pa.process_instance_id in *_(SELECT pi.process_instance_id FROM
> process_instance pi WHERE pi.user_id = '317079413683604' AND pi.app_id =
> '427380312000560')_* ORDER BY pa.process_instance_id,pa.created limit 50;
>                                                                         
>                                                                         
>                                                                  QUERY PLAN
>                                                 
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>   Limit  (cost=1071.47..1071.55 rows=31 width=24) (actual
> time=85.958..85.991 rows=50 loops=1)
>     Output: pa.process_activity_id, pa.process_instance_id, pa.created
>     Buffers: shared hit=43065
>     ->  Sort  (cost=1071.47..1071.55 rows=31 width=24) (actual
> time=85.956..85.971 rows=50 loops=1)
>           Output: pa.process_activity_id, pa.process_instance_id, pa.created
>           Sort Key: pa.process_instance_id, pa.created
>           Sort Method: top-N heapsort  Memory: 28kB
>           Buffers: shared hit=43065
>           ->  Nested Loop  (cost=1.14..1070.70 rows=31 width=24) (actual
> time=0.031..72.183 rows=46992 loops=1)
>                 Output: pa.process_activity_id, pa.process_instance_id,
> pa.created
>                 Buffers: shared hit=43065
>                 ->  Index Scan using fki_conv_konotor_user_user_id on
> public.process_instance pi  (cost=0.43..2.66 rows=1 width=8) (actual
> time=0.010..0.013 rows=2 loops=1)
>                       Output: pi.process_instance_id
>                       Index Cond: (pi.user_id = '317079413683604'::bigint)
>                       Filter: (pi.app_id = '427380312000560'::bigint)
>                       Buffers: shared hit=5
>                 ->  Index Scan using
> process_activity_process_instance_id_app_id_created_idx on
> public.process_activity pa  (cost=0.70..1053.80 rows=1425 width=24)
> (actual time=0.015..20.702 rows=*23496* loops=2)
> * Output: 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, pa.read_by_user, pa.source,
> pa.label_category_id, pa.label_id, pa.csat_response_id,
> pa.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.internal_meta, pa.interaction_id, pa.do_not_translate,
> pa.should_translate, pa.in_reply_to*
>                       Index Cond: ((pa.process_instance_id =
> pi.process_instance_id) AND (pa.app_id = '427380312000560'::bigint) AND
> (pa.created > '1970-01-01 00:00:00'::timestamp without time zone))
>                       Buffers: shared hit=43060
>   Planning time: 0.499 ms
>   Execution time: 86.040 ms
> (22 rows)
>
> *_Query 2_*
>
> web_1=>  EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT
> pa.process_activity_id AS m_process_activity_id FROM process_activity m
> WHERE pa.app_id = '427380312000560' AND pa.created > '1970-01-01
> 00:00:00' AND pa.process_instance_id in
> (*240117466018927,325820556706970,433008275197305*) ORDER BY
> pa.process_instance_id,pa.created limit 50;
>                                                                         
>                                     QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>   Limit  (cost=0.70..37.66 rows=50 width=24) (actual time=0.023..0.094
> rows=50 loops=1)
>     Output: process_activity_id, process_instance_id, created
>     Buffers: shared hit=50
>     ->  Index Scan using
> process_activity_process_instance_id_app_id_created_idx on
> public.process_activity pa  (cost=0.70..3124.97 rows=4226 width=24)
> (actual time=0.022..0.079 *rows=50* loops=1)
>           Output: process_activity_id, process_instance_id, created
>           Index Cond: ((pa.process_instance_id = ANY
> ('{140117466018927,225820556706970,233008275197305}'::bigint[])) AND
> (pa.app_id = '427380312000560'::bigint) AND (pa.created > '1970-01-01
> 00:00:00'::timestamp without time zone))
>           Buffers: shared hit=50
>   Planning time: 0.167 ms
>   Execution time: 0.137 ms
> (9 rows)
>
>
> Can someone explain
>
>   * Why It is fetching more columns and more rows, incase of inner query ?
>   * Is there any option to really limit values with INNER JOIN, INNER
>     query ? If yes, can you please share information on this ?
>
> Thanks in advance for your time and suggestions.
>
> Regards, Amar


--
Adrian Klaver
adrian.klaver@aklaver.com

On 5/7/20 4:19 AM, Amarendra Konda wrote:
> Hi,
>
> PostgreSQL version : PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled
> 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
> time) , When we pass the direct values  Vs  inner query to IN clause.
>
> High level details of the use case are as follows
>
>   * As part of the SQL there are 2 tables named Process_instance
>     (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)  between parent
>     table and child table , LIMIT is not really taking in to account.
>     Instead it is fetching more rows and columns that required, 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='317079413683604' and app_id =
'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):

SELECT
     pa.process_activity_id
FROM
     process_activity pa
JOIN
     process_instance pi
ON
     pa.process_instance_id = pi.process_instance_id
WHERE
     pa.app_id = '427380312000560'
     AND
          pa.created > '1970-01-01 00:00:00'
     AND
          pi.user_id = '317079413683604'
ORDER BY
     pa.process_instance_id,
     pa.created
LIMIT 50;

The second query is not equivalent as you are not filtering on user_id
and you are filtering on only three process_instance_id's.


>   *
>
>
> *Query1*
>
> web_1=> EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT
> pa.process_activity_id  FROM process_activity pa WHERE pa.app_id =
> '427380312000560' AND pa.created > '1970-01-01 00:00:00' AND
> pa.process_instance_id in *_(SELECT pi.process_instance_id FROM
> process_instance pi WHERE pi.user_id = '317079413683604' AND pi.app_id =
> '427380312000560')_* ORDER BY pa.process_instance_id,pa.created limit 50;
>                                                                         
>                                                                         
>                                                                  QUERY PLAN
>                                                 
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>   Limit  (cost=1071.47..1071.55 rows=31 width=24) (actual
> time=85.958..85.991 rows=50 loops=1)
>     Output: pa.process_activity_id, pa.process_instance_id, pa.created
>     Buffers: shared hit=43065
>     ->  Sort  (cost=1071.47..1071.55 rows=31 width=24) (actual
> time=85.956..85.971 rows=50 loops=1)
>           Output: pa.process_activity_id, pa.process_instance_id, pa.created
>           Sort Key: pa.process_instance_id, pa.created
>           Sort Method: top-N heapsort  Memory: 28kB
>           Buffers: shared hit=43065
>           ->  Nested Loop  (cost=1.14..1070.70 rows=31 width=24) (actual
> time=0.031..72.183 rows=46992 loops=1)
>                 Output: pa.process_activity_id, pa.process_instance_id,
> pa.created
>                 Buffers: shared hit=43065
>                 ->  Index Scan using fki_conv_konotor_user_user_id on
> public.process_instance pi  (cost=0.43..2.66 rows=1 width=8) (actual
> time=0.010..0.013 rows=2 loops=1)
>                       Output: pi.process_instance_id
>                       Index Cond: (pi.user_id = '317079413683604'::bigint)
>                       Filter: (pi.app_id = '427380312000560'::bigint)
>                       Buffers: shared hit=5
>                 ->  Index Scan using
> process_activity_process_instance_id_app_id_created_idx on
> public.process_activity pa  (cost=0.70..1053.80 rows=1425 width=24)
> (actual time=0.015..20.702 rows=*23496* loops=2)
> * Output: 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, pa.read_by_user, pa.source,
> pa.label_category_id, pa.label_id, pa.csat_response_id,
> pa.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.internal_meta, pa.interaction_id, pa.do_not_translate,
> pa.should_translate, pa.in_reply_to*
>                       Index Cond: ((pa.process_instance_id =
> pi.process_instance_id) AND (pa.app_id = '427380312000560'::bigint) AND
> (pa.created > '1970-01-01 00:00:00'::timestamp without time zone))
>                       Buffers: shared hit=43060
>   Planning time: 0.499 ms
>   Execution time: 86.040 ms
> (22 rows)
>
> *_Query 2_*
>
> web_1=>  EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT
> pa.process_activity_id AS m_process_activity_id FROM process_activity m
> WHERE pa.app_id = '427380312000560' AND pa.created > '1970-01-01
> 00:00:00' AND pa.process_instance_id in
> (*240117466018927,325820556706970,433008275197305*) ORDER BY
> pa.process_instance_id,pa.created limit 50;
>                                                                         
>                                     QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>   Limit  (cost=0.70..37.66 rows=50 width=24) (actual time=0.023..0.094
> rows=50 loops=1)
>     Output: process_activity_id, process_instance_id, created
>     Buffers: shared hit=50
>     ->  Index Scan using
> process_activity_process_instance_id_app_id_created_idx on
> public.process_activity pa  (cost=0.70..3124.97 rows=4226 width=24)
> (actual time=0.022..0.079 *rows=50* loops=1)
>           Output: process_activity_id, process_instance_id, created
>           Index Cond: ((pa.process_instance_id = ANY
> ('{140117466018927,225820556706970,233008275197305}'::bigint[])) AND
> (pa.app_id = '427380312000560'::bigint) AND (pa.created > '1970-01-01
> 00:00:00'::timestamp without time zone))
>           Buffers: shared hit=50
>   Planning time: 0.167 ms
>   Execution time: 0.137 ms
> (9 rows)
>
>
> Can someone explain
>
>   * Why It is fetching more columns and more rows, incase of inner query ?
>   * Is there any option to really limit values with INNER JOIN, INNER
>     query ? If yes, can you please share information on this ?
>
> Thanks in advance for your time and suggestions.
>
> Regards, Amar


--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, May 7, 2020 at 7:40 AM Adrian Klaver <adrian.klaver@aklaver.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, compiled
> 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
> time) , When we pass the direct values  Vs  inner query to IN clause.
>
> High level details of the use case are as follows
>
>   * As part of the SQL there are 2 tables named Process_instance
>     (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)  between parent
>     table and child table , LIMIT is not really taking in to account.
>     Instead it is fetching more rows and columns that required, 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='317079413683604' and app_id =
'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_activity pa WHERE pa.app_id = '427380312000560' AND pa.created > '1970-01-01 00:00:00'

AND EXISTS (

  SELECT 1 FROM process_instance pi WHERE pi.app_id = pa.app_id AND pi.user_id = '317079413683604'

)

ORDER BY 

pa.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 looking for.

David J.

On Thu, May 7, 2020 at 7:40 AM Adrian Klaver <adrian.klaver@aklaver.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, compiled
> 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
> time) , When we pass the direct values  Vs  inner query to IN clause.
>
> High level details of the use case are as follows
>
>   * As part of the SQL there are 2 tables named Process_instance
>     (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)  between parent
>     table and child table , LIMIT is not really taking in to account.
>     Instead it is fetching more rows and columns that required, 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='317079413683604' and app_id =
'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_activity pa WHERE pa.app_id = '427380312000560' AND pa.created > '1970-01-01 00:00:00'

AND EXISTS (

  SELECT 1 FROM process_instance pi WHERE pi.app_id = pa.app_id AND pi.user_id = '317079413683604'

)

ORDER BY 

pa.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 looking for.

David J.

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 only one column ? 

 EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)  SELECT pa.process_activity_id AS pa_process_activity_id  FROM process_activity pa WHERE pa.app_id = '126502930200650' AND pa.created > '1970-01-01 00:00:00'  AND EXISTS ( SELECT 1 FROM process_instance pi where pi.app_id = pa.app_id  AND pi.user_id = '137074931866340') ORDER BY pa.process_instance_id,m.created limit 50;
                                                                                                                                                                                                         
   QUERY PLAN                                                                                                                                                                                            
                 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------
 Limit  (cost=1.14..37.39 rows=50 width=24) (actual time=821.283..891.629 rows=50 loops=1)
   Output: pa.process_activity_id, pa.process_instance_id, pa.created
   Buffers: shared hit=274950
   ->  Nested Loop Semi Join  (cost=1.14..266660108.78 rows=367790473 width=24) (actual time=821.282..891.607 rows=50 loops=1)
         Output: pa.process_activity_id, pa.process_instance_id, pa.created
         Buffers: shared hit=274950
         ->  Index Scan using process_activity_process_instance_id_app_id_created_idx on public.process_activity pa  (cost=0.70..262062725.21 rows=367790473 width=32) (actual time=821.253..891.517 rows=50 loops=1)
               Output: 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, 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_id, pa.marketing_reply_id, pa.delivered_at, pa.reply_fragments, pa.status_fragment, pa.internal_meta, pa.interaction_id, pa.do_not_translate, pa.should_translat
e, pa.in_reply_to
               Index Cond: ((m.app_id = '126502930200650'::bigint) AND (m.created > '1970-01-01 00:00:00'::timestamp without time zone))
               Buffers: shared hit=274946
         ->  Materialize  (cost=0.43..2.66 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=50)
               Output: pi.app_id
               Buffers: shared hit=4
               ->  Index Scan using fki_conv_konotor_user_user_id on public.process_instance pi  (cost=0.43..2.66 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=1)
                     Output: pi.app_id
                     Index Cond: (pi.user_id = '137074931866340'::bigint)
                     Filter: (pi.app_id = '126502930200650'::bigint)
                     Buffers: shared hit=4
 Planning time: 0.297 ms
 Execution time: 891.686 ms
(20 rows)

On Thu, May 7, 2020 at 9:17 PM David G. Johnston <david.g.johnston@gmail.com> wrote:

On Thu, May 7, 2020 at 7:40 AM Adrian Klaver <adrian.klaver@aklaver.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, compiled
> 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
> time) , When we pass the direct values  Vs  inner query to IN clause.
>
> High level details of the use case are as follows
>
>   * As part of the SQL there are 2 tables named Process_instance
>     (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)  between parent
>     table and child table , LIMIT is not really taking in to account.
>     Instead it is fetching more rows and columns that required, 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='317079413683604' and app_id =
'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_activity pa WHERE pa.app_id = '427380312000560' AND pa.created > '1970-01-01 00:00:00'

AND EXISTS (

  SELECT 1 FROM process_instance pi WHERE pi.app_id = pa.app_id AND pi.user_id = '317079413683604'

)

ORDER BY 

pa.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 looking for.

David J.

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 only one column ? 

 EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)  SELECT pa.process_activity_id AS pa_process_activity_id  FROM process_activity pa WHERE pa.app_id = '126502930200650' AND pa.created > '1970-01-01 00:00:00'  AND EXISTS ( SELECT 1 FROM process_instance pi where pi.app_id = pa.app_id  AND pi.user_id = '137074931866340') ORDER BY pa.process_instance_id,m.created limit 50;
                                                                                                                                                                                                         
   QUERY PLAN                                                                                                                                                                                            
                 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------
 Limit  (cost=1.14..37.39 rows=50 width=24) (actual time=821.283..891.629 rows=50 loops=1)
   Output: pa.process_activity_id, pa.process_instance_id, pa.created
   Buffers: shared hit=274950
   ->  Nested Loop Semi Join  (cost=1.14..266660108.78 rows=367790473 width=24) (actual time=821.282..891.607 rows=50 loops=1)
         Output: pa.process_activity_id, pa.process_instance_id, pa.created
         Buffers: shared hit=274950
         ->  Index Scan using process_activity_process_instance_id_app_id_created_idx on public.process_activity pa  (cost=0.70..262062725.21 rows=367790473 width=32) (actual time=821.253..891.517 rows=50 loops=1)
               Output: 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, 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_id, pa.marketing_reply_id, pa.delivered_at, pa.reply_fragments, pa.status_fragment, pa.internal_meta, pa.interaction_id, pa.do_not_translate, pa.should_translat
e, pa.in_reply_to
               Index Cond: ((m.app_id = '126502930200650'::bigint) AND (m.created > '1970-01-01 00:00:00'::timestamp without time zone))
               Buffers: shared hit=274946
         ->  Materialize  (cost=0.43..2.66 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=50)
               Output: pi.app_id
               Buffers: shared hit=4
               ->  Index Scan using fki_conv_konotor_user_user_id on public.process_instance pi  (cost=0.43..2.66 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=1)
                     Output: pi.app_id
                     Index Cond: (pi.user_id = '137074931866340'::bigint)
                     Filter: (pi.app_id = '126502930200650'::bigint)
                     Buffers: shared hit=4
 Planning time: 0.297 ms
 Execution time: 891.686 ms
(20 rows)

On Thu, May 7, 2020 at 9:17 PM David G. Johnston <david.g.johnston@gmail.com> wrote:

On Thu, May 7, 2020 at 7:40 AM Adrian Klaver <adrian.klaver@aklaver.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, compiled
> 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
> time) , When we pass the direct values  Vs  inner query to IN clause.
>
> High level details of the use case are as follows
>
>   * As part of the SQL there are 2 tables named Process_instance
>     (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)  between parent
>     table and child table , LIMIT is not really taking in to account.
>     Instead it is fetching more rows and columns that required, 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='317079413683604' and app_id =
'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_activity pa WHERE pa.app_id = '427380312000560' AND pa.created > '1970-01-01 00:00:00'

AND EXISTS (

  SELECT 1 FROM process_instance pi WHERE pi.app_id = pa.app_id AND pi.user_id = '317079413683604'

)

ORDER BY 

pa.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 looking for.

David J.

Sending table structure with indexes might help little further in understanding.

Regards,

Virendra

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

Hi David,

In earlier reply, Over looked another condition, 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.

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)  SELECT pa.process_activity_id AS pa_process_activity_id  FROM process_activity pa WHERE pa.app_id = '126502930200650' AND pa.created > '1970-01-01 00:00:00'  AND EXISTS ( SELECT 1 FROM process_instance pi where pi.app_id = pa.app_id AND pi.process_instance_id = pa.process_instance_id  AND pi.user_id = '137074931866340') ORDER BY pa.process_instance_id,  pa.created limit 50;
                                                                                                                                                                                                         
      QUERY PLAN                                                                                                                                                                                          
                       
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------
 Limit  (cost=1079.44..1079.52 rows=32 width=24) (actual time=85.747..85.777 rows=50 loops=1)
   Output: pa.process_activity_id, pa.process_instance_id, pa.created
   Buffers: shared hit=43070
   ->  Sort  (cost=1079.44..1079.52 rows=32 width=24) (actual time=85.745..85.759 rows=50 loops=1)
         Output: pa.process_activity_id, pa.process_instance_id, pa.created
         Sort Key: pa.process_instance_id, pa.created
         Sort Method: top-N heapsort  Memory: 28kB
         Buffers: shared hit=43070
         ->  Nested Loop  (cost=1.14..1078.64 rows=32 width=24) (actual time=0.025..72.115 rows=47011 loops=1)
               Output: pa.process_activity_id, pa.process_instance_id, pa.created
               Buffers: shared hit=43070
               ->  Index Scan using fki_conv_konotor_user_user_id on public.process_instance pi  (cost=0.43..2.66 rows=1 width=16) (actual time=0.010..0.015 rows=2 loops=1)
                     Output: pi.app_id, pi.process_instance_id
                     Index Cond: (c.user_id = '137074931866340'::bigint)
                     Filter: (c.app_id = '126502930200650'::bigint)
                     Buffers: shared hit=5
               ->  Index Scan using process_activity_process_instance_id_app_id_created_idx on public.process_activity pa  (cost=0.70..1061.62 rows=1436 width=32) (actual time=0.011..20.320 rows=23506 loops=2)
                     Output: 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, pa.read_by_user, pa.source, pa.label_category_id, pa.label_id, pa.csat_respons
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_fragment, pa.internal_meta, pa.interaction_id, pa.do_not_translate, pa.should_tr
anslate, pa.in_reply_to
                     Index Cond: ((m.process_instance_id = pi.process_instance_id) AND (m.app_id = '126502930200650'::bigint) AND (m.created > '1970-01-01 00:00:00'::timestamp without time zone))
                     Buffers: shared hit=43065
 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 only one column ? 

 EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)  SELECT pa.process_activity_id AS pa_process_activity_id  FROM process_activity pa WHERE pa.app_id = '126502930200650' AND pa.created > '1970-01-01 00:00:00'  AND EXISTS ( SELECT 1 FROM process_instance pi where pi.app_id = pa.app_id  AND pi.user_id = '137074931866340') ORDER BY pa.process_instance_id,m.created limit 50;
                                                                                                                                                                                                         
   QUERY PLAN                                                                                                                                                                                            
                 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------
 Limit  (cost=1.14..37.39 rows=50 width=24) (actual time=821.283..891.629 rows=50 loops=1)
   Output: pa.process_activity_id, pa.process_instance_id, pa.created
   Buffers: shared hit=274950
   ->  Nested Loop Semi Join  (cost=1.14..266660108.78 rows=367790473 width=24) (actual time=821.282..891.607 rows=50 loops=1)
         Output: pa.process_activity_id, pa.process_instance_id, pa.created
         Buffers: shared hit=274950
         ->  Index Scan using process_activity_process_instance_id_app_id_created_idx on public.process_activity pa  (cost=0.70..262062725.21 rows=367790473 width=32) (actual time=821.253..891.517 rows=50 loops=1)
               Output: 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, 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_id, pa.marketing_reply_id, pa.delivered_at, pa.reply_fragments, pa.status_fragment, pa.internal_meta, pa.interaction_id, pa.do_not_translate, pa.should_translat
e, pa.in_reply_to
               Index Cond: ((m.app_id = '126502930200650'::bigint) AND (m.created > '1970-01-01 00:00:00'::timestamp without time zone))
               Buffers: shared hit=274946
         ->  Materialize  (cost=0.43..2.66 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=50)
               Output: pi.app_id
               Buffers: shared hit=4
               ->  Index Scan using fki_conv_konotor_user_user_id on public.process_instance pi  (cost=0.43..2.66 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=1)
                     Output: pi.app_id
                     Index Cond: (pi.user_id = '137074931866340'::bigint)
                     Filter: (pi.app_id = '126502930200650'::bigint)
                     Buffers: shared hit=4
 Planning time: 0.297 ms
 Execution time: 891.686 ms
(20 rows)

On Thu, May 7, 2020 at 9:17 PM David G. Johnston <david.g.johnston@gmail.com> wrote:

On Thu, May 7, 2020 at 7:40 AM Adrian Klaver <adrian.klaver@aklaver.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, compiled
> 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
> time) , When we pass the direct values  Vs  inner query to IN clause.
>
> High level details of the use case are as follows
>
>   * As part of the SQL there are 2 tables named Process_instance
>     (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)  between parent
>     table and child table , LIMIT is not really taking in to account.
>     Instead it is fetching more rows and columns that required, 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='317079413683604' and app_id =
'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_activity pa WHERE pa.app_id = '427380312000560' AND pa.created > '1970-01-01 00:00:00'

AND EXISTS (

  SELECT 1 FROM process_instance pi WHERE pi.app_id = pa.app_id AND pi.user_id = '317079413683604'

)

ORDER BY 

pa.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 looking for.

David J.

Sending table structure with indexes might help little further in understanding.

Regards,

Virendra

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

Hi David,

In earlier reply, Over looked another condition, 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.

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)  SELECT pa.process_activity_id AS pa_process_activity_id  FROM process_activity pa WHERE pa.app_id = '126502930200650' AND pa.created > '1970-01-01 00:00:00'  AND EXISTS ( SELECT 1 FROM process_instance pi where pi.app_id = pa.app_id AND pi.process_instance_id = pa.process_instance_id  AND pi.user_id = '137074931866340') ORDER BY pa.process_instance_id,  pa.created limit 50;
                                                                                                                                                                                                         
      QUERY PLAN                                                                                                                                                                                          
                       
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------
 Limit  (cost=1079.44..1079.52 rows=32 width=24) (actual time=85.747..85.777 rows=50 loops=1)
   Output: pa.process_activity_id, pa.process_instance_id, pa.created
   Buffers: shared hit=43070
   ->  Sort  (cost=1079.44..1079.52 rows=32 width=24) (actual time=85.745..85.759 rows=50 loops=1)
         Output: pa.process_activity_id, pa.process_instance_id, pa.created
         Sort Key: pa.process_instance_id, pa.created
         Sort Method: top-N heapsort  Memory: 28kB
         Buffers: shared hit=43070
         ->  Nested Loop  (cost=1.14..1078.64 rows=32 width=24) (actual time=0.025..72.115 rows=47011 loops=1)
               Output: pa.process_activity_id, pa.process_instance_id, pa.created
               Buffers: shared hit=43070
               ->  Index Scan using fki_conv_konotor_user_user_id on public.process_instance pi  (cost=0.43..2.66 rows=1 width=16) (actual time=0.010..0.015 rows=2 loops=1)
                     Output: pi.app_id, pi.process_instance_id
                     Index Cond: (c.user_id = '137074931866340'::bigint)
                     Filter: (c.app_id = '126502930200650'::bigint)
                     Buffers: shared hit=5
               ->  Index Scan using process_activity_process_instance_id_app_id_created_idx on public.process_activity pa  (cost=0.70..1061.62 rows=1436 width=32) (actual time=0.011..20.320 rows=23506 loops=2)
                     Output: 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, pa.read_by_user, pa.source, pa.label_category_id, pa.label_id, pa.csat_respons
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_fragment, pa.internal_meta, pa.interaction_id, pa.do_not_translate, pa.should_tr
anslate, pa.in_reply_to
                     Index Cond: ((m.process_instance_id = pi.process_instance_id) AND (m.app_id = '126502930200650'::bigint) AND (m.created > '1970-01-01 00:00:00'::timestamp without time zone))
                     Buffers: shared hit=43065
 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 only one column ? 

 EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)  SELECT pa.process_activity_id AS pa_process_activity_id  FROM process_activity pa WHERE pa.app_id = '126502930200650' AND pa.created > '1970-01-01 00:00:00'  AND EXISTS ( SELECT 1 FROM process_instance pi where pi.app_id = pa.app_id  AND pi.user_id = '137074931866340') ORDER BY pa.process_instance_id,m.created limit 50;
                                                                                                                                                                                                         
   QUERY PLAN                                                                                                                                                                                            
                 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------
 Limit  (cost=1.14..37.39 rows=50 width=24) (actual time=821.283..891.629 rows=50 loops=1)
   Output: pa.process_activity_id, pa.process_instance_id, pa.created
   Buffers: shared hit=274950
   ->  Nested Loop Semi Join  (cost=1.14..266660108.78 rows=367790473 width=24) (actual time=821.282..891.607 rows=50 loops=1)
         Output: pa.process_activity_id, pa.process_instance_id, pa.created
         Buffers: shared hit=274950
         ->  Index Scan using process_activity_process_instance_id_app_id_created_idx on public.process_activity pa  (cost=0.70..262062725.21 rows=367790473 width=32) (actual time=821.253..891.517 rows=50 loops=1)
               Output: 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, 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_id, pa.marketing_reply_id, pa.delivered_at, pa.reply_fragments, pa.status_fragment, pa.internal_meta, pa.interaction_id, pa.do_not_translate, pa.should_translat
e, pa.in_reply_to
               Index Cond: ((m.app_id = '126502930200650'::bigint) AND (m.created > '1970-01-01 00:00:00'::timestamp without time zone))
               Buffers: shared hit=274946
         ->  Materialize  (cost=0.43..2.66 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=50)
               Output: pi.app_id
               Buffers: shared hit=4
               ->  Index Scan using fki_conv_konotor_user_user_id on public.process_instance pi  (cost=0.43..2.66 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=1)
                     Output: pi.app_id
                     Index Cond: (pi.user_id = '137074931866340'::bigint)
                     Filter: (pi.app_id = '126502930200650'::bigint)
                     Buffers: shared hit=4
 Planning time: 0.297 ms
 Execution time: 891.686 ms
(20 rows)

On Thu, May 7, 2020 at 9:17 PM David G. Johnston <david.g.johnston@gmail.com> wrote:

On Thu, May 7, 2020 at 7:40 AM Adrian Klaver <adrian.klaver@aklaver.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, compiled
> 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
> time) , When we pass the direct values  Vs  inner query to IN clause.
>
> High level details of the use case are as follows
>
>   * As part of the SQL there are 2 tables named Process_instance
>     (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)  between parent
>     table and child table , LIMIT is not really taking in to account.
>     Instead it is fetching more rows and columns that required, 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='317079413683604' and app_id =
'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_activity pa WHERE pa.app_id = '427380312000560' AND pa.created > '1970-01-01 00:00:00'

AND EXISTS (

  SELECT 1 FROM process_instance pi WHERE pi.app_id = pa.app_id AND pi.user_id = '317079413683604'

)

ORDER BY 

pa.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 looking for.

David J.