Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dpDs0-0000N0-OR for pgsql-performance@arkaria.postgresql.org; Tue, 05 Sep 2017 13:26:24 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dpDs0-00079k-0W for pgsql-performance@arkaria.postgresql.org; Tue, 05 Sep 2017 13:26:24 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dpDqD-0003ym-5r for pgsql-performance@postgresql.org; Tue, 05 Sep 2017 13:24:33 +0000 Received: from mail-io0-x234.google.com ([2607:f8b0:4001:c06::234]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dpDq6-0007Fb-LM for pgsql-performance@postgresql.org; Tue, 05 Sep 2017 13:24:32 +0000 Received: by mail-io0-x234.google.com with SMTP id q64so16033133iod.5 for ; Tue, 05 Sep 2017 06:24: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=WaTfSw6Y/KSKyJ/i0hmXsYIvBPTDunfK5YJSPxkzSaA=; b=p+TNQ9m9SFwE4otByRfQr0W0YQT/GzXLx+2nyOknGZlg+8RbdqMHI7UICqf+B1EPki hxkVJhu3WrfhqGZiGV+jVHTQfAUu7ZnAb2eqfrX3ERR2eaaQFHXyOY1aph7O4YweXFcM Zmv2nIO83D5KDQ6sJ9OPSjLF6QstbA0z67IF26FWQSYOF1ABW8rNRFjdReXFfDnkftQ3 RwrWhzfTVgwmGoUwBw1/ItPYai/GLio9qMlc5bckUxd48BYwDN9CrVINa8/cZXhT4fB4 JvxD0/n5dIKmjvBXSMwyVrrCOCLDe5uYG/atcDNR5SyMtvLOuZpk1+0RZwWpRNzFkIL9 G8Xw== 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=WaTfSw6Y/KSKyJ/i0hmXsYIvBPTDunfK5YJSPxkzSaA=; b=lRZWWrzdz4zFBPAaJ935FhTKC/8idOLN/aBDARV8p9vonPtTzRA+2u+jKtwJ26m7aH 6oIGFCQpF+w7D05G7lYb3CFETfJZFKN/SCJumfXila2VDlvjewgN/neWlHB/jV4hs5uP hyKC1tjvTFAUPP6zMmTsihmVHAB5zUHsUDQLMOCgIbDN0kpV4cXjJoTenWL2aotQG/LI 7LKxip4TYCRwizJKD0mzfDfECiRi/VMQxvlug8sDr9Inn/jiY/XmdEK7g5+305T059bq 5GpnGfZUDDMg7oJlxRPiBSG7zuG0j0CYwcBMXsCazH4ICOiUB8ZiX9mVyaXQWqUKytR1 e/hg== X-Gm-Message-State: AHPjjUhVTZkmXz0I5PCZJpwMtB51mwOk+3SwRA2R4xb1XgP+hTkLhCls 2/ePGzhyegRFMSCST7+NQ5lrie4LwbwR X-Google-Smtp-Source: ADKCNb4M3elvZCZS7ezXeX1G55WRdR+PolsCHyEw0L4ULm+x03etD0n00PGh8a2w7UZM70G4kVpcUE204rACzs1V5U0= X-Received: by 10.36.36.67 with SMTP id f64mr4657729ita.10.1504617865324; Tue, 05 Sep 2017 06:24:25 -0700 (PDT) MIME-Version: 1.0 Received: by 10.79.140.148 with HTTP; Tue, 5 Sep 2017 06:24:24 -0700 (PDT) From: Soni M Date: Tue, 5 Sep 2017 20:24:24 +0700 Message-ID: Subject: slow index scan performance To: "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="001a1146ea9062b02f05587125f8" 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 --001a1146ea9062b02f05587125f8 Content-Type: text/plain; charset="UTF-8" Consider these 2 index scan produced by a query -> Index Scan using response_log_by_activity on public.response_log rl2 (cost=0.00..51.53 rows=21 width=8) (actual time=9.017..9.056 rows=0 loops=34098) Output: rl2.activity_id, rl2.feed_id Index Cond: (rl2.activity_id = rl.activity_id) Filter: rl2.success Buffers: shared hit=3357159 read=153313 -> Index Scan using activity_pkey on public.activity a (cost=0.00..51.10 rows=1 width=12) (actual time=0.126..0.127 rows=1 loops=34088) Output: a.status_id, a.activity_id, a.visit_id Index Cond: (a.activity_id = rl.activity_id) Buffers: shared hit=137925 read=32728 And it's size conscopy=# select pg_size_pretty(pg_relation_size('response_log_by_activity'::regclass)); pg_size_pretty ---------------- 7345 MB (1 row) conscopy=# select pg_size_pretty(pg_relation_size('activity_pkey'::regclass)); pg_size_pretty ---------------- 8110 MB (1 row) Index scan on response_log_by_activity is far slower. The table has just been repacked, and index rebuilt, but still slow. Is there any other way to make it faster ? Why Buffers: shared hit=3,357,159 read=153,313 on response_log_by_activity is much bigger than Buffers: shared hit=137925 read=32728 on activity_pkey while activity_pkey size is bigger ? -- Regards, Soni Maula Harriz --001a1146ea9062b02f05587125f8 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Consider these 2 index scan produced by a query

-> =C2=A0Index Scan using response_log_by_activity on publ= ic.response_log rl2 =C2=A0(cost=3D0.00..51.53 rows=3D21 width=3D8) (actual = time=3D9.017..9.056 rows=3D0 loops=3D34098)
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Output: rl2.activity_id, rl2.f= eed_id
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0Index Cond: (rl2.activity_id =3D rl.activity_id)
=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Filter: rl2.succ= ess
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0Buffers: shared hit=3D3357159 read=3D153313
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0-> =C2=A0Index Scan using activity_pkey on public.activity a = =C2=A0(cost=3D0.00..51.10 rows=3D1 width=3D12) (actual time=3D0.126..0.127 = rows=3D1 loops=3D34088)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0Output: a.status_id, a.activity_id, a.visit_id
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (a.activity_id =3D rl.activity_i= d)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit= =3D137925 read=3D32728


And it's= size

conscopy=3D# select pg_size_pretty(pg_r= elation_size('response_log_by_activity'::regclass));
=C2= =A0pg_size_pretty=C2=A0
----------------
=C2=A07345 MB<= /div>
(1 row)

conscopy=3D# select pg_size_pret= ty(pg_relation_size('activity_pkey'::regclass));
=C2=A0pg= _size_pretty=C2=A0
----------------
=C2=A08110 MB
=
(1 row)

Index scan on response_log_by_a= ctivity is far slower. The table has just been repacked, and index rebuilt,= but still slow.

Is there any other way to make it faster ?

W= hy Buffers: shared hit=3D3,357,159 read=3D153,313 on response_log_by_activi= ty is much bigger than Buffers: shared hit=3D137925 read=3D32728 on activit= y_pkey while activity_pkey size is bigger ?

--
Regards,

Soni Maula Harriz
--001a1146ea9062b02f05587125f8--