Обсуждение: [GENERAL] Slow index scan - Pgsql 9.2

Hi guys,

I've got the following Query:

WITH

                                       query_p AS (

                                               SELECT CAST(6667176 AS BIGINT) AS client_id),

                                                                                               

 clients AS (

                                                   SELECT

                                                       client.id,client.job_share_mode

                                                       FROM

                                                           customers AS client

                                                       WHERE

                                                           (client.clientid = (SELECT qp.client_id FROM query_p AS qp))

                                                       AND

                                                           NOT client.is_demo

                                                       AND

                                                           NOT client.deleted

                                                   )

Select qp.client_id, (SELECT COUNT(0) FROM customers AS c WHERE (c.clientid = qp.client_id) AND NOT c.deleted) AS client_count

FROM query_p AS qp

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??

Table customers:

                                              Table "public.customers"

         Column         |            Type             |                            Modifiers                            

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

 id                     | bigint                      | not null default "nextval"('"customers_seq"'::"regclass")

 clientid               | bigint                      | not null default 0

 name_first             | character varying(80)       | default ''::character varying

 name_last              | character varying(80)       | default ''::character varying

 company                | character varying(255)      | default ''::character varying

Index clientid_customers:

CREATE INDEX

    clientid_customers

ON

    customers

    (

        "clientid"

    );

Thanks!

Patrick

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.

I see.. The deleted column is: 

deleted boolean

Should I create an index for that? How could I improve this query?

Does it execute as slowly when you run it for a 2nd time?

No, it doesn't. I think it's because of cache?

 

I would think because of the NOT "deleted" clause. Which is interesting, because that's a column which you conveniently didn't include in the definition below.

My mistake.

Would an Index be sufficient to solve the problem?

On 01/10/2017 04:05 AM, Patrick B wrote:

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


I see.. The deleted column is:

deleted boolean

Should I create an index for that? How could I improve this query?


    Does it execute as slowly when you run it for a 2nd time?


No, it doesn't. I think it's because of cache?


    I would think because of the NOT "deleted" clause. Which is
    interesting, because that's a column which you conveniently didn't
    include in the definition below.


My mistake.


Would an Index be sufficient to solve the problem?

Not a separate index - the query probably would not benefit from two separate indexes. But you can amend the existing index, to allow index-only scans, i.e. creating an index like this:

  CREATE INDEX ON (clientid, is_demo, deleted, id, job, job_share_mode)

This will make the index larger, but it should allow index-only scans.

The other thing you could try is partial index, i.e.

  CREATE INDEX ON (clientid) WHERE NOT is_demo AND NOT deleted;

You can also combine those approaches, but you'll have to include all columns into the index, even those in the index predicate:

  CREATE INDEX ON (clientid, is_demo, deleted, id, job, job_share_mode)
  WHERE NOT is_demo AND NOT deleted;

I'd bet all of those will outperform the current plan.

regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services