pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedExplain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )
13+ messages / 6 participants
[nested] [flat]
* Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )
@ 2020-05-07 11:19 Amarendra Konda <amar.vijaya@gmail.com>
2020-05-07 14:40 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 13+ messages in thread
From: Amarendra Konda @ 2020-05-07 11:19 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org; pgsql-general@postgresql.org
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
-
*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.marketing_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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )
2020-05-07 11:19 Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
@ 2020-05-07 14:40 ` Adrian Klaver <adrian.klaver@aklaver.com>
2020-05-07 15:46 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) David G. Johnston <david.g.johnston@gmail.com>
2020-05-07 17:36 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
0 siblings, 2 replies; 13+ messages in thread
From: Adrian Klaver @ 2020-05-07 14:40 UTC (permalink / raw)
To: Amarendra Konda <amar.vijaya@gmail.com>; pgsql-performance@lists.postgresql.org; pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )
2020-05-07 11:19 Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
2020-05-07 14:40 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Adrian Klaver <adrian.klaver@aklaver.com>
@ 2020-05-07 15:46 ` David G. Johnston <david.g.johnston@gmail.com>
2020-05-07 17:49 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: David G. Johnston @ 2020-05-07 15:46 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Amarendra Konda <amar.vijaya@gmail.com>; pgsql-performance@lists.postgresql.org, pgsql-general <pgsql-general@postgresql.org>
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )
2020-05-07 11:19 Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
2020-05-07 14:40 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Adrian Klaver <adrian.klaver@aklaver.com>
2020-05-07 15:46 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) David G. Johnston <david.g.johnston@gmail.com>
@ 2020-05-07 17:49 ` Amarendra Konda <amar.vijaya@gmail.com>
2020-05-07 18:07 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
2020-05-07 19:25 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Adrian Klaver <adrian.klaver@aklaver.com>
2020-05-07 21:21 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 3 replies; 13+ messages in thread
From: Amarendra Konda @ 2020-05-07 17:49 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-performance@lists.postgresql.org, pgsql-general <pgsql-general@postgresql.org>
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_translate, 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.
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )
2020-05-07 11:19 Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
2020-05-07 14:40 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Adrian Klaver <adrian.klaver@aklaver.com>
2020-05-07 15:46 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) David G. Johnston <david.g.johnston@gmail.com>
2020-05-07 17:49 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
@ 2020-05-07 18:07 ` Amarendra Konda <amar.vijaya@gmail.com>
2020-05-07 18:51 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
2020-05-07 21:59 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 2 replies; 13+ messages in thread
From: Amarendra Konda @ 2020-05-07 18:07 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-performance@lists.postgresql.org, pgsql-general <pgsql-general@postgresql.org>
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_translate, 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.
>>
>>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )
2020-05-07 11:19 Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
2020-05-07 14:40 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Adrian Klaver <adrian.klaver@aklaver.com>
2020-05-07 15:46 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) David G. Johnston <david.g.johnston@gmail.com>
2020-05-07 17:49 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
2020-05-07 18:07 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
@ 2020-05-07 18:51 ` Amarendra Konda <amar.vijaya@gmail.com>
2020-05-07 19:30 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Virendra Kumar <viru_7683@yahoo.com>
1 sibling, 1 reply; 13+ messages in thread
From: Amarendra Konda @ 2020-05-07 18:51 UTC (permalink / raw)
To: Virendra Kumar <viru_7683@yahoo.com>; +Cc: pgsql-performance@lists.postgresql.org, pgsql-general <pgsql-general@postgresql.org>
Hi Virendra,
Thanks for your time.
Here is the table and index structure
* process_activity*
Table "public.process_activity"
Column | Type | Modifiers
--------------------+-----------------------------+----------------------------
process_activity_id | bigint | not null
default next_id()
process_activity_type | smallint | not null
voice_url | text |
process_activity_user_id | bigint | not null
app_id | bigint | not null
process_instance_id | bigint | not null
alias | text | not null
read_by_user | smallint | default 0
source | smallint | default 0
label_category_id | bigint |
label_id | bigint |
csat_response_id | bigint |
process_activity_fragments | jsonb |
created | timestamp without time zone | not null
updated | timestamp without time zone |
rule_id | bigint |
marketing_reply_id | bigint |
delivered_at | timestamp without time zone |
reply_fragments | jsonb |
status_fragment | jsonb |
internal_meta | jsonb |
interaction_id | text |
do_not_translate | boolean |
should_translate | integer |
in_reply_to | jsonb |
Indexes:
"process_activity_pkey" PRIMARY KEY, btree (process_activity_id)
"fki_process_activity_konotor_user_user_id" btree
(process_activity_user_id) WITH (fillfactor='70')
"*process_activity_process_instance_id_app_id_created_idx*" btree
(process_instance_id, app_id, created) WITH (fillfactor='70')
"process_activity_process_instance_id_app_id_read_by_user_created_idx"
btree (process_instance_id, app_id, read_by_user, created) WITH
(fillfactor='70')
"process_activity_process_instance_id_idx" btree (process_instance_id)
WITH (fillfactor='70')
*process_instance*
Table "public.process_instance"
Column | Type | Modifiers
-------------------------+-----------------------------+-----------------------------
process_instance_id | bigint | not null default
next_id()
process_instance_alias | text | not null
app_id | bigint | not null
user_id | bigint | not null
Indexes:
"process_instance_pkey" PRIMARY KEY, btree (process_instance_id)
"*fki_conv_konotor_user_user_id*" btree (user_id) WITH (fillfactor='70')
Regards, Amarendra
On Fri, May 8, 2020 at 12:01 AM Virendra Kumar <viru_7683@yahoo.com> wrote:
> 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_translate, 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.
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )
2020-05-07 11:19 Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
2020-05-07 14:40 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Adrian Klaver <adrian.klaver@aklaver.com>
2020-05-07 15:46 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) David G. Johnston <david.g.johnston@gmail.com>
2020-05-07 17:49 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
2020-05-07 18:07 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
2020-05-07 18:51 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
@ 2020-05-07 19:30 ` Virendra Kumar <viru_7683@yahoo.com>
0 siblings, 0 replies; 13+ messages in thread
From: Virendra Kumar @ 2020-05-07 19:30 UTC (permalink / raw)
To: Amarendra Konda <amar.vijaya@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org, pgsql-general <pgsql-general@postgresql.org>
Here is my thought on why row is 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 columns 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.
When in join condition it has to fetch rows according to filter clause, join 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 = '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 the query.
Regards,
Virendra
On Thursday, May 7, 2020, 11:52:00 AM PDT, Amarendra Konda <amar.vijaya@gmail.com> wrote:
Hi Virendra,
Thanks for your time.
Here is the table and index structure
process_activity
Table "public.process_activity"
Column | Type | Modifiers
--------------------+-----------------------------+----------------------------
process_activity_id | bigint | not null default next_id()
process_activity_type | smallint | not null
voice_url | text |
process_activity_user_id | bigint | not null
app_id | bigint | not null
process_instance_id | bigint | not null
alias | text | not null
read_by_user | smallint | default 0
source | smallint | default 0
label_category_id | bigint |
label_id | bigint |
csat_response_id | bigint |
process_activity_fragments | jsonb |
created | timestamp without time zone | not null
updated | timestamp without time zone |
rule_id | bigint |
marketing_reply_id | bigint |
delivered_at | timestamp without time zone |
reply_fragments | jsonb |
status_fragment | jsonb |
internal_meta | jsonb |
interaction_id | text |
do_not_translate | boolean |
should_translate | integer |
in_reply_to | jsonb |
Indexes:
"process_activity_pkey" PRIMARY KEY, btree (process_activity_id)
"fki_process_activity_konotor_user_user_id" btree (process_activity_user_id) WITH (fillfactor='70')
"process_activity_process_instance_id_app_id_created_idx" btree (process_instance_id, app_id, created) WITH (fillfactor='70')
"process_activity_process_instance_id_app_id_read_by_user_created_idx" btree (process_instance_id, app_id, read_by_user, created) WITH (fillfactor='70')
"process_activity_process_instance_id_idx" btree (process_instance_id) WITH (fillfactor='70')
process_instance
Table "public.process_instance"
Column | Type | Modifiers
-------------------------+-----------------------------+-----------------------------
process_instance_id | bigint | not null default next_id()
process_instance_alias | text | not null
app_id | bigint | not null
user_id | bigint | not null
Indexes:
"process_instance_pkey" PRIMARY KEY, btree (process_instance_id)
"fki_conv_konotor_user_user_id" btree (user_id) WITH (fillfactor='70')
Regards, Amarendra
On Fri, May 8, 2020 at 12:01 AM Virendra Kumar <viru_7683@yahoo.com> wrote:
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )
2020-05-07 11:19 Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
2020-05-07 14:40 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Adrian Klaver <adrian.klaver@aklaver.com>
2020-05-07 15:46 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) David G. Johnston <david.g.johnston@gmail.com>
2020-05-07 17:49 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
2020-05-07 18:07 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
@ 2020-05-07 21:59 ` David G. Johnston <david.g.johnston@gmail.com>
2020-05-07 23:46 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) David Rowley <dgrowleyml@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: David G. Johnston @ 2020-05-07 21:59 UTC (permalink / raw)
To: Amarendra Konda <amar.vijaya@gmail.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-performance@lists.postgresql.org, pgsql-general <pgsql-general@postgresql.org>
On Thu, May 7, 2020 at 11:07 AM Amarendra Konda <amar.vijaya@gmail.com>
wrote:
> 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;
>
>
>
>
> -> 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)*
>
> 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))
I suppose during the nested loop the inner index scan could limit itself to
the first 50 entries it finds (since the first two index columns are being
held constant on each scan, m.created should define the traversal order...)
so that the output of the nested loop ends up being (max 2 x 50) 100
entries which are then sorted and only the top 50 returned.
Whether the executor could but isn't doing that here or isn't programmed to
do that (or my logic is totally off) I do not know.
David J.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )
2020-05-07 11:19 Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
2020-05-07 14:40 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Adrian Klaver <adrian.klaver@aklaver.com>
2020-05-07 15:46 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) David G. Johnston <david.g.johnston@gmail.com>
2020-05-07 17:49 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
2020-05-07 18:07 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
2020-05-07 21:59 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) David G. Johnston <david.g.johnston@gmail.com>
@ 2020-05-07 23:46 ` David Rowley <dgrowleyml@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: David Rowley @ 2020-05-07 23:46 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Amarendra Konda <amar.vijaya@gmail.com>; Adrian Klaver <adrian.klaver@aklaver.com>; Pgsql Performance <pgsql-performance@lists.postgresql.org>; pgsql-general <pgsql-general@postgresql.org>
On Fri, 8 May 2020 at 10:00, David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Thu, May 7, 2020 at 11:07 AM Amarendra Konda <amar.vijaya@gmail.com> wrote:
>>
>> 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;
>>
>>
>> -> 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)
>
> > 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))
>
> I suppose during the nested loop the inner index scan could limit itself to the first 50 entries it finds (since the first two index columns are being held constant on each scan, m.created should define the traversal order...) so that the output of the nested loop ends up being (max 2 x 50) 100 entries which are then sorted and only the top 50 returned.
>
> Whether the executor could but isn't doing that here or isn't programmed to do that (or my logic is totally off) I do not know.
I think the planner is likely not putting the process_activity table
on the outer side of the nested loop join due to the poor row
estimates. If it knew that so many rows would match the join then it
likely would have done that to save from having to perform the sort at
all. However, because the planner has put the process_instance on the
outer side of the nested loop join, it's the pathkeys from that path
that the nested loop node has, which is not the same as what the ORDER
BY needs, so the planner must add a sort step, which means that all
rows from the nested loop plan must be read so that they can be
sorted.
It might be worth trying: create index on process_instance
(user_id,app_id); as that might lower the cost of performing the join
in the opposite order and have the planner prefer that order instead.
If doing that, the OP could then ditch the
fki_conv_konotor_user_user_id index to save space.
If that's not enough to convince the planner that the opposite order
is better then certainly SET enable_sort TO off; would.
David
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )
2020-05-07 11:19 Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
2020-05-07 14:40 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Adrian Klaver <adrian.klaver@aklaver.com>
2020-05-07 15:46 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) David G. Johnston <david.g.johnston@gmail.com>
2020-05-07 17:49 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
@ 2020-05-07 19:25 ` Adrian Klaver <adrian.klaver@aklaver.com>
2 siblings, 0 replies; 13+ messages in thread
From: Adrian Klaver @ 2020-05-07 19:25 UTC (permalink / raw)
To: Amarendra Konda <amar.vijaya@gmail.com>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org, pgsql-general <pgsql-general@postgresql.org>
On 5/7/20 10:49 AM, Amarendra Konda wrote:
> Hi David,
>
> Thanks for the reply.This has optimized number of rows.
Yeah, but your execution time has increased an order of magnitude. Not
sure if that is what you want.
>
> 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 <mailto:david.g.johnston@gmail.com>> wrote:
>
> On Thu, May 7, 2020 at 7:40 AM Adrian Klaver
> <adrian.klaver@aklaver.com <mailto: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'
> ANDEXISTS (
> 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.
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )
2020-05-07 11:19 Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
2020-05-07 14:40 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Adrian Klaver <adrian.klaver@aklaver.com>
2020-05-07 15:46 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) David G. Johnston <david.g.johnston@gmail.com>
2020-05-07 17:49 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
@ 2020-05-07 21:21 ` David G. Johnston <david.g.johnston@gmail.com>
2020-05-07 21:26 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 13+ messages in thread
From: David G. Johnston @ 2020-05-07 21:21 UTC (permalink / raw)
To: Amarendra Konda <amar.vijaya@gmail.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-performance@lists.postgresql.org, pgsql-general <pgsql-general@postgresql.org>
On Thu, May 7, 2020 at 10:49 AM Amarendra Konda <amar.vijaya@gmail.com>
wrote:
> Can you please explain, why it is getting more columns in output, even
> though we have asked for only one column ?
>
>
>
> * 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_translate, pa.in_reply_to*
>
Not knowing the source code in this area at all...
I'm pretty sure its because it doesn't matter. The executor retrieves data
"pages", 8k blocks containing multiple records, then extracts specific full
tuples from there. At that point its probably just data pointers being
passed around. Its not until the end that the planner/executor has to
decide which subset of columns to return to the user, or when a new tuple
structure has to be created anyway (say because of joining), maybe, does it
take the effort of constructing a minimally necessary output column set.
David J.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )
2020-05-07 11:19 Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
2020-05-07 14:40 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Adrian Klaver <adrian.klaver@aklaver.com>
2020-05-07 15:46 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) David G. Johnston <david.g.johnston@gmail.com>
2020-05-07 17:49 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
2020-05-07 21:21 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) David G. Johnston <david.g.johnston@gmail.com>
@ 2020-05-07 21:26 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 13+ messages in thread
From: Tom Lane @ 2020-05-07 21:26 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Amarendra Konda <amar.vijaya@gmail.com>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-performance@lists.postgresql.org, pgsql-general <pgsql-general@postgresql.org>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thu, May 7, 2020 at 10:49 AM Amarendra Konda <amar.vijaya@gmail.com>
> wrote:
>> Can you please explain, why it is getting more columns in output, even
>> though we have asked for only one column ?
>> * 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_translate, pa.in_reply_to*
> Not knowing the source code in this area at all...
> I'm pretty sure its because it doesn't matter.
It's actually intentional, to save a projection step within that plan
node. We'll discard the extra columns once it matters, at some higher
plan level.
(There have been some debates on -hackers about whether this optimization
is still worth anything, given all the executor improvements that have
been made since it went in. But it was clearly a win at the time.)
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query )
2020-05-07 11:19 Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
2020-05-07 14:40 ` Re: Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Adrian Klaver <adrian.klaver@aklaver.com>
@ 2020-05-07 17:36 ` Amarendra Konda <amar.vijaya@gmail.com>
1 sibling, 0 replies; 13+ messages in thread
From: Amarendra Konda @ 2020-05-07 17:36 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-performance@lists.postgresql.org; pgsql-general@postgresql.org
Hi Adrian,
Thanks for the reply. And i have kept latest execution plans, for various
SQL statements ( inner join, sub queries and placing values instead of sub
query) .
As suggested, tried with INNER JOIN, however result was similar to
subquery.
Is there any way we can tell the optimiser to process less number of rows
based on the LIMIT value ? ( i.e. may be SQL re-write) ?
*INNER SQL*
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT pi.process_instance_id AS
pi_process_instance_id FROM process_instance pi WHERE pi.user_id =
'137074931866340' AND pi.app_id = '126502930200650';
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
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.018..0.019 rows=2
loops=1)*
Output: process_instance_id
Index Cond: (pi.user_id = '137074931866340'::bigint)
Filter: (pi.app_id = '126502930200650'::bigint)
Buffers: shared hit=5
Planning time: 0.119 ms
Execution time: 0.041 ms
*Full query - Sub query*
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
pa.process_instance_id in (SELECT pi.process_instance_id AS
pi_process_instance_id FROM process_instance pi WHERE pi.user_id =
'137074931866340' AND pi.app_id = '126502930200650') ORDER BY
pa.process_instance_id, pa.created limit 50;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------
Limit (cost=1072.91..1072.99 rows=31 width=24) (actual
time=744.386..744.415 rows=50 loops=1)
Output: pa.process_activity_id, pa.process_instance_id, pa.created
Buffers: shared hit=3760 read=39316
-> Sort (cost=1072.91..1072.99 rows=31 width=24) (actual
time=744.384..744.396 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=3760 read=39316
-> Nested Loop (cost=1.14..1072.14 rows=31 width=24) (actual
time=0.044..727.297 rows=47011 loops=1)
Output: pa.process_activity_id, pa.process_instance_id,
pa.created
Buffers: shared hit=3754 read=39316
-> 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.009..0.015 rows=2 loops=1)*
Output: pi.process_instance_id
Index Cond: (pi.user_id = '137074931866340'::bigint)
Filter: (pi.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..1055.22 rows=1427 width=24) *(actual
time=0.029..349.000 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: ((pa.process_instance_id =
pi.process_instance_id) AND (pa.app_id = '126502930200650'::bigint) AND
(pa.created > '1970-01-01 00:00:00'::timestamp without time zone))
Buffers: shared hit=3749 read=39316
Planning time: 2.547 ms
Execution time: 744.499 ms
(22 rows)
*Full query - INNER JOIN*
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT pa.process_activity_id
AS pa_process_activity_id FROM process_activity pa INNER JOIN
process_instance pi ON pi.process_instance_id = pa.process_instance_id AND
pa.app_id = '126502930200650' AND pa.created > '1970-01-01 00:00:00' AND
pi.user_id = '137074931866340' AND pi.app_id = '126502930200650' ORDER BY
pa.process_instance_id, pa.created limit 50;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------
Limit (cost=1072.91..1072.99 rows=31 width=24) (actual
time=87.803..87.834 rows=50 loops=1)
Output: pa.process_activity_id, pa.process_instance_id, pa.created
Buffers: shared hit=43070
-> Sort (cost=1072.91..1072.99 rows=31 width=24) (actual
time=87.803..87.815 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..1072.14 rows=31 width=24) (actual
time=0.030..73.847 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=8) *(actual
time=0.015..0.018 rows=2 loops=1)*
Output: pi.process_instance_id
Index Cond: (pi.user_id = '137074931866340'::bigint)
Filter: (pi.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..1055.22 rows=1427 width=24) *(actual
time=0.011..21.447 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: ((pa.process_instance_id =
pi.process_instance_id) AND (pa.app_id = '126502930200650'::bigint) AND
(pa.created > '1970-01-01 00:00:00'::timestamp without time zone))
Buffers: shared hit=43065
Planning time: 0.428 ms
Execution time: 87.905 ms
*FULL Query - INNER SQL replaced with result*
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) SELECT pa.process_activity_id AS
m_process_activity_id FROM process_activity pa WHERE pa.app_id =
'126502930200650' AND pa.created > '1970-01-01 00:00:00' AND
pa.process_instance_id in (*137074941043913,164357609323111*) ORDER BY
pa.process_instance_id,pa.created limit 50;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------
Limit (cost=0.70..37.65 rows=50 width=24) (actual time=0.016..0.095
rows=50 loops=1)
Output: process_activity_id, process_instance_id, created
Buffers: shared hit=55
-> Index Scan using
process_activity_process_instance_id_app_id_created_idx on
public.process_activity pa (cost=0.70..2100.39 rows=2841 width=24) *(actual
time=0.015..0.077 rows=50 loops=1)*
Output: process_activity_id, process_instance_id, created
Index Cond: ((pa.process_instance_id = ANY
('{137074941043913,164357609323111}'::bigint[])) AND (pa.app_id =
'126502930200650'::bigint) AND (m.created > '1970-01-01
00:00:00'::timestamp without time
zone))
Buffers: shared hit=55
Planning time: 1.710 ms
Execution time: 0.147 ms
Regards, Amar
On Thu, May 7, 2020 at 8:10 PM 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):
>
> 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
>
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2020-05-07 23:46 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-05-07 11:19 Explain plan changes - IN CLAUSE ( Passing direct values Vs INNER Query ) Amarendra Konda <amar.vijaya@gmail.com>
2020-05-07 14:40 ` Adrian Klaver <adrian.klaver@aklaver.com>
2020-05-07 15:46 ` David G. Johnston <david.g.johnston@gmail.com>
2020-05-07 17:49 ` Amarendra Konda <amar.vijaya@gmail.com>
2020-05-07 18:07 ` Amarendra Konda <amar.vijaya@gmail.com>
2020-05-07 18:51 ` Amarendra Konda <amar.vijaya@gmail.com>
2020-05-07 19:30 ` Virendra Kumar <viru_7683@yahoo.com>
2020-05-07 21:59 ` David G. Johnston <david.g.johnston@gmail.com>
2020-05-07 23:46 ` David Rowley <dgrowleyml@gmail.com>
2020-05-07 19:25 ` Adrian Klaver <adrian.klaver@aklaver.com>
2020-05-07 21:21 ` David G. Johnston <david.g.johnston@gmail.com>
2020-05-07 21:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-05-07 17:36 ` Amarendra Konda <amar.vijaya@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox