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

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.

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