Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dkA5c-000366-9P for pgsql-performance@arkaria.postgresql.org; Tue, 22 Aug 2017 14:23:32 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dkA5b-0007vk-HV for pgsql-performance@arkaria.postgresql.org; Tue, 22 Aug 2017 14:23:31 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dkA5a-0007tj-O9 for pgsql-performance@postgresql.org; Tue, 22 Aug 2017 14:23:30 +0000 Received: from mail-wr0-x229.google.com ([2a00:1450:400c:c0c::229]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dkA5W-0007qb-VH for pgsql-performance@postgresql.org; Tue, 22 Aug 2017 14:23:30 +0000 Received: by mail-wr0-x229.google.com with SMTP id k46so52343423wre.2 for ; Tue, 22 Aug 2017 07:23:26 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:from:date:message-id:subject:to; bh=YofTGDOUN4lnVEOyXU0lgKXdRXK+wtKu7sP1zOcIk/c=; b=QQ0EssPmL5kLlz2TyOS4rvUsjY94LSnUwY5QOlKJ+2ZfyUsdKEEus84jaW0S5WIqY2 IKhDKuAUbIC46PmEbz5iTX0nn/C4rui8UhCGRT9ZYKTEuvwBJJdj7eDP7k8jbvZhpj5j Icih1P7GZL737w/WKBG64x8EB07ujKqZqXCjhDh6GnryG2pmaDScs8gLTre9U2eR1klJ 3JvpQFyP8LlQlVlltIV3jNjFSHiEcdGMZS5Nhe0YVGAcaiP4srh74KLxDdfBlbqclo71 Bo7zOMgMjlisDG16ieOoZbN1UrJhnpOd+/AnzqVTln3v/NNxg3eOXVeAasCTE1h7VkHQ D9HQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:from:date:message-id:subject:to; bh=YofTGDOUN4lnVEOyXU0lgKXdRXK+wtKu7sP1zOcIk/c=; b=o1LQ0z9yzo302NC7BVatdH4UuO9tqjzg36XZSGNOrSc6Y9tCtD3gDPxDDSx49njMvI nFeCOpQlNCFZgN/ipq8c8LWdyEE0aAwnoQCoWZOGBZbwQ/J4AZ4cqZYaaksJ++2dmiUA SG3zes4xo7eMhhuGLannBhIF/3el2DlB5SEv7Ey5wEAdrUYy+jHbEwYJVm8WIStuV9mg drxzPl7+H4vjbOprh5Ws9czbwFz1qAZWI9/G0EVgVLr/uoXGe2PixYeJYteB65EDOz+R hNLDEDHB+6822mKsImomVm0vy5yJT3iHX8HhwINDvRDfQTSn9x0EJkdUs6pMyELkXfFi XHwA== X-Gm-Message-State: AHYfb5hKhbRkZFL+HIcwXzNE2Q3yhoxd4F8ZT8OTWzTiQwtuQlsrg/9m QsZcxS5UIGlLUQ4nFOcTghEmql5SjF8YbSU= X-Received: by 10.223.173.182 with SMTP id w51mr630022wrc.113.1503411805383; Tue, 22 Aug 2017 07:23:25 -0700 (PDT) MIME-Version: 1.0 Received: by 10.28.184.85 with HTTP; Tue, 22 Aug 2017 07:23:24 -0700 (PDT) From: Mariel Cherkassky Date: Tue, 22 Aug 2017 17:23:24 +0300 Message-ID: Subject: query runs for more than 24 hours! To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary="f403045cf65e9c72050557585696" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --f403045cf65e9c72050557585696 Content-Type: text/plain; charset="UTF-8" Hi, I have a query that I run in my postgresql 9.6 database and it runs for more than 24 hours and doesnt finish. My select consist from few joins : SELECT a.inst_prod_id, product_id, nap_area2, nap_phone_num, nap_product_id, b.nap_discount_num, b.nap_makat_cd, nap_act_start_dt, b.nap_debt_line, nap_act_end_dt, b.row_added_dttm b.row_lastmant_dttm, FROM ps_rf_inst_prod a, AND a.setid || ''= 'SHARE' nap_ip_discount b WHERE nap_crm_status = 'C_04' AND b.nap_makat_cd IN (SELECT term_code AND b.setid || ''= 'SHARE' AND a.inst_prod_id = b.inst_prod_id AND start_date <= b.nap_rishum_date FROM tv_finterm WHERE pricing_method_code in ('2', '4') AND coalesce(end_date, to_date('01/01/2095','DD/MM/YYYY')) != AND coalesce(end_date, to_date('01/01/2095','DD/MM/YYYY')) >= b.nap_rishum_date start_date) AND (b.row_lastmant_dttm > to_date('01/01/2005','DD/MM/YYYY') OR AND b.nap_act_end_dt > clock_timestamp() AND TRUNC(b.nap_act_start_dt) < TRUNC(b.nap_act_end_dt) b.nap_rishum_date > to_date('01/01/2005','DD/MM/YYYY') OR WHERE PERCENT IS NOT NULL b.row_added_dttm > to_date('01/01/2005','DD/MM/YYYY')) AND b.nap_discount_num IN (SELECT k.discount_line FROM tv_discounts_details k AND k.start_month = 1) AND c.phone = a.nap_phone_num AND (NOT EXISTS(SELECT /*+index(c TC_FINTERMS_I_SERVICE) */ 1 FROM tc_finterms c WHERE c.area = a.nap_area2 AND c.term_code = b.nap_makat_cd WHERE service_uid = (a.inst_prod_id)::integer AND deb_cred_line_no = b.nap_debt_line AND (payment_end_date > clock_timestamp()) AND term_type = '2') OR NOT EXISTS(SELECT 1 FROM ip_service_discounts AND service_code = b.nap_makat_cd and b.nap_purch_instprod = ' '; AND discount_code = b.nap_discount_num AND (end_date IS NULL OR coalesce(discount_end_date, clock_timestamp() + interval '1 days') > clock_timestamp()))) Before trying to work on performance I checked locks and nothing returned : =# select a1.query as blocking_query, a2. query as waiting_query, t.schemaname ||'.'||t.relname as locked_table from pg_stat_activity a1 join pg_locks p1 on a1. pid = p1.pid and p1.granted join pg_locks pg_stat_activity a2 on a2. pid = p2.pid join pg_stat_all_tables t on p2 on p1.relation = p2.relation and not p2.granted join p1.relation = t.relid; (0 rows) blocking_query | waiting_query | locked_table ----------------+---------------+-------------- I checked the explain plan of my query : Nested Loop Semi Join (cost=0.43..7565655389.26 rows=1 width=93) Join Filter: (b.nap_discount_num = (k.discount_line)::numeric) -> Seq Scan on ps_rf_inst_prod a (cost=0.00..4337158.91 rows=40452 width=41) -> Nested Loop (cost=0.43..7565653159.07 rows=2 width=93) -> Index Scan using ps_nap_ip_discount on nap_ip_discount b (cost=0.43..186920.69 rows=1 width=60) Filter: (((nap_crm_status)::text = 'C_04'::text) AND (((setid)::text || ''::text) = 'SHARE'::text)) Filter: (((nap_purch_instprod)::text = ' '::text) AND (nap_act_end_dt > clock_timestamp()) AND (((setid)::text || ''::t Index Cond: ((inst_prod_id)::text = (a.inst_prod_id)::text) e('01/01/2005'::text, 'DD/MM/YYYY'::text)) OR (nap_rishum_date > to_date('01/01/2005'::text, 'DD/MM/YYYY'::text)) OR (row_added_dttm > ext) = 'SHARE'::text) AND (trunc(nap_act_start_dt, 'DDD'::text) < trunc(nap_act_end_dt, 'DDD'::text)) AND ((row_lastmant_dttm > to_dat -> Index Scan using tc_finterms_ix1 on tc_finterms c (cost=0.56..8.60 rows=1 width=0) to_date('01/01/2005'::text, 'DD/MM/YYYY'::text))) AND ((NOT (SubPlan 2)) OR (NOT (SubPlan 3))) AND (SubPlan 1)) SubPlan 2 b.nap_makat_cd) AND (deb_cred_line_no = (b.nap_debt_line)::double precision)) Index Cond: (((area)::text = (a.nap_area2)::text) AND ((phone)::text = (a.nap_phone_num)::text)) Filter: (((term_type)::text = '2'::text) AND (payment_end_date > clock_timestamp()) AND ((term_code)::numeric = Filter: (((service_code)::numeric = b.nap_makat_cd) AND ((discount_code)::numeric = b.nap_discount_num) AND ((e SubPlan 3 -> Index Scan using ip_service_discounts_pkey on ip_service_discounts (cost=0.56..10.78 rows=1 width=0) Index Cond: (service_uid = (a.inst_prod_id)::integer) Recheck Cond: (((pricing_method_code)::text = ANY ('{2,4}'::text[])) AND (start_date <= b.nap_rishum_date)) nd_date IS NULL) OR (COALESCE((discount_end_date)::timestamp with time zone, (clock_timestamp() + '1 day'::interval)) > clock_timestam p()))) SubPlan 1 -> Bitmap Heap Scan on tv_finterm (cost=2290.83..17301.61 rows=26907 width=4) -> Bitmap Index Scan on index_test_mariel (cost=0.00..2284.11 rows=81126 width=0) Filter: ((COALESCE(end_date, (to_date('01/01/2095'::text, 'DD/MM/YYYY'::text))::timestamp without time zone) >= b.nap_rishum_date) AND (COALESCE(end_date, (to_date('01/01/2095'::text, 'DD/MM/YYYY'::text))::timestamp without time zone) <> start_d ate)) (25 rows) Index Cond: (((pricing_method_code)::text = ANY ('{2,4}'::text[])) AND (start_date <= b.nap_rishum_date)) -> Materialize (cost=0.00..1407.38 rows=43933 width=4) -> Seq Scan on tv_discounts_details k (cost=0.00..1187.71 rows=43933 width=4) Filter: ((percent IS NOT NULL) AND (start_month = 1)) I run vacuum analyze database before running the query. Some info about the tables : ps_rf_inst_prod - 32G nap_ip_discount-1G tv_finterm - 100M tc_finterms - 6G TV_FINTERM - 1G This query is part of an app that I migrated from oracle to postgresql. I dont want to change the query much, looking for a way to change the plan to make it faster.. I have indexes on ps_rf_inst_prod, when I delete the pipelines in : AND a.setid || ''= 'SHARE' AND b.setid || ''= 'SHARE' the plan is changing and it uses indexes on ps_rf_inst_prod but it costs more and the performance are worse. Please , HELP... --f403045cf65e9c72050557585696 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Hi, I ha= ve a query that I run in my postgresql 9.6 database and it runs for more th= an 24 hours and doesnt finish.

My select consist from few joins :

SELECT a.inst_prod_id,
                                           product_id,
                                           nap_area2,

                                           nap_phone_num,=

                                           nap_product_id,

                                           b.nap_discount_num,
                                           b.nap_makat_cd,

                                           nap_act_start_dt,
                                           b.nap_debt_line,
                                           nap_act_end_dt,

                                           b.row_added_dttm
                                           b.row_lastmant_dttm,
                                    FROM   ps_rf_inst_prod a,

                                    AND    a.setid  || ''=
;=3D 'SHARE'
                                          =
 nap_ip_discount b
                                    WHERE  nap_crm_status  =3D 'C_04=
9;

                                    AND    b.nap_makat_cd IN (SE=
LECT term_code
                                    AND    b.set=
id  || ''=3D 'SHARE'
                                    AND    a.inst_prod_id =3D b.inst_prod_i=
d

                                             AND    start_date &=
lt;=3D b.nap_rishum_date
                                      =
       FROM   tv_finterm
                                             WHERE  pricing_method_code in =
('2', '4')

                                             AND    coalesce(end=
_date, to_date('01/01/2095','DD/MM/YYYY')) !=3D                                             AND    coalesce(end_date, to_=
date('01/01/2095','DD/MM/YYYY')) >=3D
                                                    b.nap_rishum_date
                                                    start_date)

                                    AND (b.row_lastmant_dttm >=
;  to_date('01/01/2005','DD/MM/YYYY') OR
      =
                              AND    b.nap_act_end_dt > clock_timestamp(=
)
                                    AND    TRUNC(b.nap_act_start_dt) < T=
RUNC(b.nap_act_end_dt)
                                          b.nap_rishum_date > to_date(&#=
39;01/01/2005','DD/MM/YYYY') OR

                                             WHERE  PERCENT IS N=
OT NULL
                                          b.row_added_d=
ttm > to_date('01/01/2005','DD/MM/YYYY'))
                                    AND   b.nap_discount_num IN (SELECT k.d=
iscount_line
                                             FROM   tv_discounts_details k
                                             AND    k.start_month =3D 1)

                                                        AND    c=
.phone =3D a.nap_phone_num
                                    =
AND (NOT EXISTS(SELECT /*+index(c TC_FINTERMS_I_SERVICE) */
                                                         1
                                                        FROM   tc_finterms =
c
                                                        WHERE  c.area =3D a=
.nap_area2
                                                        AND    c.term_code =
=3D b.nap_makat_cd

                                             WHERE  service_uid =
=3D (a.inst_prod_id)::integer
                                 =
                       AND    deb_cred_line_no =3D b.nap_debt_line
                                                        AND (payment_end_da=
te > clock_timestamp())
                                                        AND    term_type =
=3D '2')
                                    OR  NOT EXISTS(SELECT 1
                                             FROM   ip_service_discounts
                                             AND    service_code =3D b.nap_=
makat_cd

                                              and  b.nap_purch_i=
nstprod  =3D ' ';
                                     =
        AND    discount_code =3D b.nap_discount_num

                                             AND (end_date IS NU=
LL OR coalesce(discount_end_date, clock_timestamp() + interval '1 days&=
#39;) > clock_timestamp())))

Before trying to work on performance I chec= ked locks and nothing returned :

=3D# select a1.query as blocking_query, a2. query as waiting=
_query, 
        t.schemaname ||=
'.'||t.relname as locked_table from pg_stat_activity            
        a1 join pg_locks p1 on a1. pid =3D p1.pid and p1.granted =
join pg_locks=20

        pg_stat_activity a2 on a2. pid =3D p2.pid join pg_stat_a=
ll_tables t on 
        p2 on p1.relation =3D p2.relation and n=
ot p2.granted join=20
         p1.relation =3D t.relid;

     (0 rows)
        blocking_query | waiting_query=
 | locked_table

        ----------------+---------------+--------------

I checke= d the explain plan of my query :

 Nested Loop Semi Join  (cost=3D0.43..7565655389.26 rows=3D1=
 width=3D93)
   Join Filter: (b.nap_discoun=
t_num =3D (k.discount_line)::numeric)

         ->  Seq Scan on ps_rf_inst_prod a  (cost=3D0.00..433=
7158.91     rows=3D40452 width=3D41)
   ->  Nested Loop  (co=
st=3D0.43..7565653159.07 rows=3D2 width=3D93)

         ->  Index Scan using ps_nap_ip_discount on nap_ip_di=
scount b      (cost=3D0.43..186920.69 rows=3D1 width=3D60)
    =
           Filter: (((nap_crm_status)::text =3D 'C_04'::text) AND  =
   (((setid)::text || ''::text) =3D 'SHARE'::text))

               Filter: (((nap_purch_instprod)::text =3D ' &#=
39;::text) AND     (nap_act_end_dt > clock_timestamp()) AND (((setid)::t=
ext || ''::t
               Index Cond: ((inst_prod_id)=
::text =3D (a.inst_prod_id)::text)

e('01/01/2005'::text, 'DD/MM/YYYY'::text)) OR (n=
ap_rishum_date >     to_date('01/01/2005'::text, 'DD/MM/YYYY=
'::text)) OR (row_added_dttm >
ext) =3D 'SHARE':=
:text) AND (trunc(nap_act_start_dt, 'DDD'::text) <     trunc(nap=
_act_end_dt, 'DDD'::text)) AND ((row_lastmant_dttm > to_dat

                 ->  Index Scan using tc_finterms_ix1 on tc_f=
interms c  (cost=3D0.56..8.60 rows=3D1 width=3D0)
 to_date('=
;01/01/2005'::text, 'DD/MM/YYYY'::text))) AND ((NOT (SubPlan 2)=
) OR     (NOT (SubPlan 3))) AND (SubPlan 1))
               SubPlan 2

 b.nap_makat_cd) AND (deb_cred_line_no =3D (b.nap_debt_line)::do=
uble     precision))
                   Index Cond: (((area)::t=
ext =3D (a.nap_area2)::text) AND ((phone)::text =3D (a.nap_phone_num)::text=
))
                   Filter: (((term_type)::text =3D '2'::text) AND  =
   (payment_end_date > clock_timestamp()) AND ((term_code)::numeric =3D

                       Filter: (((service_code)::numeric =3D b.n=
ap_makat_cd)     AND ((discount_code)::numeric =3D b.nap_discount_num) AND =
((e
               SubPlan 3
                 ->  Index Scan using ip_service_discounts_pkey on     i=
p_service_discounts  (cost=3D0.56..10.78 rows=3D1 width=3D0)
                       Index Cond: (service_uid =3D (a.inst_prod_id)::integ=
er)

                       Recheck Cond: (((pricing_method_code)::te=
xt =3D ANY     ('{2,4}'::text[])) AND (start_date <=3D b.nap_ris=
hum_date))
nd_date IS NULL) OR (COALESCE((discount_end_date)::t=
imestamp with time zone,     (clock_timestamp() + '1 day'::interval=
)) > clock_timestam
p())))
                  SubPlan 1
                 ->  Bitmap Heap Scan on tv_finterm  (cost=3D2290.83..17=
301.61     rows=3D26907 width=3D4)

                       ->  Bitmap Index Scan on index_test_ma=
riel      (cost=3D0.00..2284.11 rows=3D81126 width=3D0)
       =
                Filter: ((COALESCE(end_date,     (to_date('01/01/2095&#=
39;::text, 'DD/MM/YYYY'::text))::timestamp without time zone) >=
=3D
 b.nap_rishum_date) AND (COALESCE(end_date, (to_date('01/01/2095'::=
text,     'DD/MM/YYYY'::text))::timestamp without time zone) <&g=
t; start_d
ate))

(25 rows)
                             Index Cond: (=
((pricing_method_code)::text =3D ANY     ('{2,4}'::text[])) AND (st=
art_date <=3D b.nap_rishum_date))
   ->  Materialize  (cost=3D0.00..1407.38 rows=3D43933 width=3D4)
         ->  Seq Scan on tv_discounts_details k  (cost=3D0.00..1187.71 r=
ows=3D43933 width=3D4)

               Filter: ((percent IS NOT NULL) AND (start_month =
=3D 1))

I run=C2=A0vacuum analyze database=C2=A0before running the qu= ery. Some info about the tables :

ps_rf_inst_prod - 32G 
nap_=
ip_discount-1G=20

tv_finterm - 100M
tc_finterms - 6G

TV_FINTERM -  1G

This query is part of an app that I migrated fr= om oracle to postgresql. I dont want to change the query much, looking for = a way to change the plan to make it faster.. I have indexes on ps_rf_inst_p= rod, when I delete the pipelines in :

  AND    a.setid  || ''=3D 'SHARE'
  AND    b.setid  || ''=3D &=
#39;SHARE'

the plan is changing and it uses indexes on ps_rf_inst_prod = but it costs more and the performance are worse.

Please , HELP...

--f403045cf65e9c72050557585696--