Re: Slow index scan - Pgsql 9.2

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Mon, Jan 9, 2017 at 6:06 PM, Patrick B <patrickbakerbr@xxxxxxxxx> wrote:

Explain Analyze:

CTE Scan on "query_p" "qp"  (cost=0.01..1060.57 rows=1 width=8) (actual time=4065.244..4065.246 rows=1 loops=1)

  CTE query_p

    ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.003 rows=1 loops=1)

  SubPlan 2

    ->  Aggregate  (cost=1060.53..1060.54 rows=1 width=0) (actual time=4065.229..4065.229 rows=1 loops=1)

          ->  Index Scan using "clientid_customers" on "customers" "c"  (cost=0.00..1059.01 rows=607 width=0) (actual time=9.105..4063.728 rows=2513 loops=1)

                Index Cond: ("clientid" = "qp"."client_id")

                Filter: (NOT "deleted")

                Rows Removed by Filter: 1068

Total runtime: 4075.753 ms

Why a search for "client_id" is so slow??

​3,581​ individual pokes into the heap to confirm tuple visibility and apply the deleted filter - that could indeed take a while.

David J.