Search Postgresql Archives

Re: Slow index scan - Pgsql 9.2

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux