Query time related to limit clause - Mailing list pgsql-general

From Shubham Mittal
Subject Query time related to limit clause
Date
Msg-id CA+ERcR9j1a0eYOHY=VOjW3iFJKqn6qAzUS=h5X8hO2EDmhwE1w@mail.gmail.com
Whole thread Raw
Responses Re: Query time related to limit clause
List pgsql-general
Hi Team,

I have shared execution times of two queries below:

I need to find only the first row matching the criteria , but limit 1 is taking more time than limit 15 or more.. If any one can tell an explanation for this and how I can achieve the same in less time. 

 explain analyze SELECT * from abc where organisation_process_path = cast('org' as ltree) and  abc_type='secondary' and common_details->'commonDetails'->'nchBundle'->>'subGridName'='905811-22_MISCN_data'
            and status <>
               'CLOSED' AND sub_product_type = 'Prepaid'
            AND created_date >= cast('2021-03-23 00:00:00.000' AS TIMESTAMP)
        AND created_date <= cast('2021-09-23 00:00:00.000' AS TIMESTAMP)
order by created_date asc LIMIT 1

"Limit  (cost=1.31..941.32 rows=1 width=6947) (actual time=5117.039..5117.042 rows=1 loops=1)"
"  ->  Merge Append  (cost=1.31..4476296.09 rows=4762 width=6947) (actual time=5117.036..5117.038 rows=1 loops=1)"
"        Sort Key: abc_serv_nch_q1_2021.created_date"
"        ->  Index Scan using abc_serv_nch_q1_2021_created_date_idx on abc_serv_nch_q1_2021  (cost=0.43..378412.39 rows=1005 width=7025) (actual time=742.277..742.277 rows=0 loops=1)"
"              Index Cond: ((created_date >= '2021-03-23 00:00:00'::timestamp without time zone) AND (created_date <= '2021-09-23 00:00:00'::timestamp without time zone))"
"              Filter: (((status)::text <> 'CLOSED'::text) AND (organisation_process_path = 'org'::ltree) AND ((abc_type)::text = 'secondary'::text) AND ((sub_product_type)::text = 'Prepaid'::text) AND ((((common_details -> 'commonDetails'::text) -> 'nchBundle'::text) ->> 'subGridName'::text) = '905811-22_MISCN_data'::text))"
"              Rows Removed by Filter: 558116"
"        ->  Index Scan using abc_serv_nch_q2_2021_created_date_idx on abc_serv_nch_q2_2021  (cost=0.43..2674454.09 rows=3756 width=6928) (actual time=2074.950..2074.950 rows=1 loops=1)"
"              Index Cond: ((created_date >= '2021-03-23 00:00:00'::timestamp without time zone) AND (created_date <= '2021-09-23 00:00:00'::timestamp without time zone))"
"              Filter: (((status)::text <> 'CLOSED'::text) AND (organisation_process_path = 'org'::ltree) AND ((abc_type)::text = 'secondary'::text) AND ((sub_product_type)::text = 'Prepaid'::text) AND ((((common_details -> 'commonDetails'::text) -> 'nchBundle'::text) ->> 'subGridName'::text) = '905811-22_MISCN_data'::text))"
"              Rows Removed by Filter: 1743539"
"        ->  Index Scan using abc_serv_nch_q3_2021_created_date_idx on abc_serv_nch_q3_2021  (cost=0.43..1423368.04 rows=1 width=6548) (actual time=2299.805..2299.805 rows=0 loops=1)"
"              Index Cond: ((created_date >= '2021-03-23 00:00:00'::timestamp without time zone) AND (created_date <= '2021-09-23 00:00:00'::timestamp without time zone))"
"              Filter: (((status)::text <> 'CLOSED'::text) AND (organisation_process_path = 'org'::ltree) AND ((abc_type)::text = 'secondary'::text) AND ((sub_product_type)::text = 'Prepaid'::text) AND ((((common_details -> 'commonDetails'::text) -> 'nchBundle'::text) ->> 'subGridName'::text) = '905811-22_MISCN_data'::text))"
"              Rows Removed by Filter: 1320434"


"Planning Time: 18.563 ms"
"Execution Time: 5117.157 ms"

***********************WHEN LIMIT IS GIVEN MORE THAN EQUAL TO 15************************

explain analyze SELECT * from abc where organisation_process_path = cast('org' as ltree) and  abc_type='secondary' and common_details->'commonDetails'->'nchBundle'->>'subGridName'='905811-22_MISCN_data'
            and status <>
               'CLOSED' AND sub_product_type = 'Prepaid'
            AND created_date >= cast('2021-03-23 00:00:00.000' AS TIMESTAMP)
        AND created_date <= cast('2021-09-23 00:00:00.000' AS TIMESTAMP)
order by created_date asc LIMIT 15

       "Limit  (cost=12708.06..12708.09 rows=15 width=6947) (actual time=0.428..0.431 rows=15 loops=1)"
"  ->  Sort  (cost=12708.06..12719.96 rows=4762 width=6947) (actual time=0.426..0.428 rows=15 loops=1)"
"        Sort Key: abc_serv_nch_q1_2021.created_date"
"        Sort Method: top-N heapsort  Memory: 40kB"
"        ->  Append  (cost=7201.82..12591.22 rows=4762 width=6947) (actual time=0.081..0.366 rows=299 loops=1)"
"              ->  Bitmap Heap Scan on abc_serv_nch_q1_2021  (cost=7201.82..8338.60 rows=1005 width=7025) (actual time=0.038..0.038 rows=0 loops=1)"
"                    Recheck Cond: (((((common_details -> 'commonDetails'::text) -> 'nchBundle'::text) ->> 'subGridName'::text) = '905811-22_MISCN_data'::text) AND ((sub_product_type)::text = 'Prepaid'::text) AND ((abc_type)::text = 'secondary'::text) AND ((status)::text <> 'CLOSED'::text) AND (created_date >= '2021-03-23 00:00:00'::timestamp without time zone) AND (created_date <= '2021-09-23 00:00:00'::timestamp without time zone))"
"                    Filter: (organisation_process_path = 'org'::ltree)"
"                    ->  BitmapAnd  (cost=7201.82..7201.82 rows=1005 width=0) (actual time=0.036..0.037 rows=0 loops=1)"
"                          ->  Bitmap Index Scan on abc_serv_nch_q1_2021_expr_idx3  (cost=0.00..195.83 rows=14010 width=0) (actual time=0.036..0.036 rows=0 loops=1)"
"                                Index Cond: ((((common_details -> 'commonDetails'::text) -> 'nchBundle'::text) ->> 'subGridName'::text) = '905811-22_MISCN_data'::text)"
"                          ->  Bitmap Index Scan on abc_serv_nch_q1_2021_created_date_idx  (cost=0.00..7005.23 rows=533170 width=0) (never executed)"
"                                Index Cond: ((created_date >= '2021-03-23 00:00:00'::timestamp without time zone) AND (created_date <= '2021-09-23 00:00:00'::timestamp without time zone))"
"              ->  Index Scan using abc_serv_nch_q2_2021_expr_idx3 on abc_serv_nch_q2_2021  (cost=0.43..4226.46 rows=3756 width=6928) (actual time=0.042..0.305 rows=299 loops=1)"
"                    Index Cond: ((((common_details -> 'commonDetails'::text) -> 'nchBundle'::text) ->> 'subGridName'::text) = '905811-22_MISCN_data'::text)"
"                    Filter: ((created_date >= '2021-03-23 00:00:00'::timestamp without time zone) AND (created_date <= '2021-09-23 00:00:00'::timestamp without time zone) AND (organisation_process_path = 'org'::ltree))"
"              ->  Index Scan using abc_serv_nch_q3_2021_expr_idx3 on abc_serv_nch_q3_2021  (cost=0.12..2.35 rows=1 width=6548) (actual time=0.003..0.003 rows=0 loops=1)"
"                    Index Cond: ((((common_details -> 'commonDetails'::text) -> 'nchBundle'::text) ->> 'subGridName'::text) = '905811-22_MISCN_data'::text)"
"                    Filter: ((created_date >= '2021-03-23 00:00:00'::timestamp without time zone) AND (created_date <= '2021-09-23 00:00:00'::timestamp without time zone) AND (organisation_process_path = 'org'::ltree))"


"Planning Time: 21.959 ms"
"Execution Time: 0.551 ms"


Thanks ,
Shubham

pgsql-general by date:

Previous
From: Israel Brewster
Date:
Subject: Re: Growth planning
Next
From: Rob Sargent
Date:
Subject: Re: Growth planning