Message-Id: <1390833860.7369.75825537.0CAF7BA8@webmail.messagingengine.com>
From: Stelian Iancu <stelian@iancu.ch>
To: pgsql-performance@postgresql.org
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain
Subject: Slow query (wrong index used maybe)
Date: Mon, 27 Jan 2014 06:44:20 -0800
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hello,

I have Postrgres 9.3 running on a Linux machine with 32GB RAM. I have a
fairly large database (some tables with approx. 1 mil. records) and I
have the following query:

    SELECT * FROM (
    SELECT DISTINCT c.ext_content_id AS type_1_id,
    "substring"(c.ext_content_id::text, 1, 13) AS type_1_album_id,
    cm1.value AS type_1_artist,
    cm2.value AS type_1_title,
    cm4.value AS type_1_duration,
    pm1.value AS type_1_icpn,
    cm3.value AS type_1_isrc,
    c.provider AS type_1_provider,
    to_number(cm5.value::text, '999999'::text) AS type_2_set_number,
    to_number(cm6.value::text, '999999'::text) AS type_2_track_number,
    cm7.value AS type_6_availability_ppd,
    cm12.value AS type_6_availability_sub,
    cm9.value AS type_1_language,
    cm11.value AS type_1_label_reporting_id,
    cm13.value AS type_1_parent_isrc
    FROM content c
    LEFT JOIN content_metadata cm1 ON c.content_id = cm1.content_id AND
    cm1.name::text = 'track_artist'::text
    LEFT JOIN content_metadata cm2 ON c.content_id = cm2.content_id AND
    cm2.name::text = 'track_title'::text
    LEFT JOIN content_metadata cm3 ON c.content_id = cm3.content_id AND
    cm3.name::text = 'track_isrc'::text
    LEFT JOIN content_metadata cm4 ON c.content_id = cm4.content_id AND
    cm4.name::text = 'track_duration'::text
    LEFT JOIN content_metadata cm5 ON c.content_id = cm5.content_id AND
    cm5.name::text = 'set_number'::text
    LEFT JOIN content_metadata cm6 ON c.content_id = cm6.content_id AND
    cm6.name::text = 'track_number'::text
    LEFT JOIN content_metadata cm7 ON c.content_id = cm7.content_id AND
    cm7.name::text = 'unlimited'::text
    LEFT JOIN content_metadata cm9 ON c.content_id = cm9.content_id AND
    cm9.name::text = 'language'::text
    LEFT JOIN content_metadata cm10 ON c.content_id = cm10.content_id
    AND cm10.name::text = 'import_date'::text
    LEFT JOIN content_metadata cm11 ON c.content_id = cm11.content_id
    AND cm11.name::text = 'label_reporting_id'::text
    LEFT JOIN content_metadata cm12 ON c.content_id = cm12.content_id
    AND cm12.name::text = 'subscription'::text
    LEFT JOIN content_metadata cm13 ON c.content_id = cm13.content_id
    AND cm13.name::text = 'parent_isrc'::text,
    product p
    LEFT JOIN product_metadata pm4 ON p.product_id = pm4.product_id AND
    pm4.name::text = 'product_title'::text
    LEFT JOIN product_metadata pm1 ON p.product_id = pm1.product_id AND
    pm1.name::text = 'upc'::text
    WHERE p.ext_product_id::text = substr(c.ext_content_id::text, 1, 13)
    ) view
    WHERE type_1_id='1-111-1027897-01-001';

Below are the definitions of the tables involved.

Content:

                      Table "public.content"
         Column      |            Type             | Modifiers
    -----------------+-----------------------------+-----------
     content_id      | bigint                      | not null
     status          | character varying(3)        | not null
     display_name    | character varying(1024)     | not null
     ext_content_id  | character varying(64)       | not null
     provider        | character varying(128)      | not null
     last_updated_by | character varying(30)       | not null
     last_updated_on | timestamp without time zone | not null
     created_by      | character varying(30)       | not null
     created_on      | timestamp without time zone | not null
    Indexes:
        "content_pkey" PRIMARY KEY, btree (content_id)
        "ak_key_2_content" UNIQUE, btree (ext_content_id, provider)
        "index_content_01" UNIQUE, btree (ext_content_id)
    Foreign-key constraints:
        "fk_content_01" FOREIGN KEY (provider) REFERENCES
        provider(ext_provider_id)
    Referenced by:
        TABLE "content_metadata" CONSTRAINT "fk_content_metadata_01"
        FOREIGN KEY (content_id) REFERENCES content(content_id)
        TABLE "packaged" CONSTRAINT "fk_packaged_reference_content"
        FOREIGN KEY (content_id) REFERENCES content(content_id)
        TABLE "product_content" CONSTRAINT "fk_product_content_01"
        FOREIGN KEY (content_id) REFERENCES content(content_id)
    Triggers:
        td_content BEFORE DELETE ON content FOR EACH ROW EXECUTE
        PROCEDURE trigger_fct_td_content()
        ti_content BEFORE INSERT ON content FOR EACH ROW EXECUTE
        PROCEDURE trigger_fct_ti_content()
        tu_content BEFORE UPDATE ON content FOR EACH ROW EXECUTE
        PROCEDURE trigger_fct_tu_content()
        tu_content_tree BEFORE UPDATE ON content FOR EACH ROW EXECUTE
        PROCEDURE trigger_fct_tu_content_tree()

Product:

                  Table "public.product"
         Column      |            Type             | Modifiers
    -----------------+-----------------------------+-----------
     product_id      | bigint                      | not null
     status          | character varying(3)        | not null
     display_name    | character varying(1024)     | not null
     ext_product_id  | character varying(64)       | not null
     last_updated_by | character varying(30)       | not null
     last_updated_on | timestamp without time zone | not null
     created_by      | character varying(30)       | not null
     created_on      | timestamp without time zone | not null
    Indexes:
        "product_pkey" PRIMARY KEY, btree (product_id)
        "ak_key_2_product" UNIQUE, btree (ext_product_id)
    Referenced by:
        TABLE "contract_product" CONSTRAINT "fk_contract_product_02"
        FOREIGN KEY (product_id) REFERENCES product(product_id)
        TABLE "offer_product" CONSTRAINT "fk_offer_product_01" FOREIGN
        KEY (product_id) REFERENCES product(product_id)
        TABLE "product_metadata" CONSTRAINT
        "fk_product__reference_product" FOREIGN KEY (product_id)
        REFERENCES product(product_id)
        TABLE "product_content" CONSTRAINT "fk_product_content_02"
        FOREIGN KEY (product_id) REFERENCES product(product_id)
    Triggers:
        td_product BEFORE DELETE ON product FOR EACH ROW EXECUTE
        PROCEDURE trigger_fct_td_product()
        ti_product BEFORE INSERT ON product FOR EACH ROW EXECUTE
        PROCEDURE trigger_fct_ti_product()
        tu_product BEFORE UPDATE ON product FOR EACH ROW EXECUTE
        PROCEDURE trigger_fct_tu_product()
        tu_product_tree BEFORE UPDATE ON product FOR EACH ROW EXECUTE
        PROCEDURE trigger_fct_tu_product_tree()

Product_metadata:

              Table "public.product_metadata"
         Column      |            Type             | Modifiers
    -----------------+-----------------------------+-----------
     product_id      | bigint                      | not null
     name            | character varying(64)       | not null
     distributor_id  | bigint                      |
     value           | character varying(4000)     |
     created_on      | timestamp without time zone | not null
     created_by      | character varying(30)       | not null
     last_updated_on | timestamp without time zone | not null
     last_updated_by | character varying(30)       | not null
    Indexes:
        "idx_product_metadata_03" btree (name, value)
        "index_product_metadata_02" btree (product_id, name)
        "index_product_metadata_cid" btree (product_id)
    Foreign-key constraints:
        "fk_product__reference_product" FOREIGN KEY (product_id)
        REFERENCES product(product_id)
        "fk_product_metadata_02" FOREIGN KEY (distributor_id) REFERENCES
        operator(operator_id)
    Triggers:
        td_product_metadata BEFORE DELETE ON product_metadata FOR EACH
        ROW EXECUTE PROCEDURE trigger_fct_td_product_metadata()
        ti_product_metadata BEFORE INSERT ON product_metadata FOR EACH
        ROW EXECUTE PROCEDURE trigger_fct_ti_product_metadata()
        tu_product_metadata BEFORE UPDATE ON product_metadata FOR EACH
        ROW EXECUTE PROCEDURE trigger_fct_tu_product_metadata()

Content_metadata:

              Table "public.content_metadata"
         Column      |            Type             | Modifiers
    -----------------+-----------------------------+-----------
     content_id      | bigint                      | not null
     name            | character varying(64)       | not null
     distributor_id  | bigint                      |
     value           | character varying(4000)     |
     last_updated_by | character varying(30)       | not null
     last_updated_on | timestamp without time zone | not null
     created_by      | character varying(30)       | not null
     created_on      | timestamp without time zone | not null
    Indexes:
        "idx_content_metadata_03" btree (name, value)
        "idx_content_metadata_04" btree (content_id, name, value)
        "index_content_metadata_02" btree (content_id, name)
        "index_content_metadata_cid" btree (content_id)
    Foreign-key constraints:
        "fk_content_metadata_01" FOREIGN KEY (content_id) REFERENCES
        content(content_id)
        "fk_content_metadata_02" FOREIGN KEY (distributor_id) REFERENCES
        operator(operator_id)
    Triggers:
        td_content_metadata BEFORE DELETE ON content_metadata FOR EACH
        ROW EXECUTE PROCEDURE trigger_fct_td_content_metadata()
        ti_content_metadata BEFORE INSERT ON content_metadata FOR EACH
        ROW EXECUTE PROCEDURE trigger_fct_ti_content_metadata()
        tu_content_metadata BEFORE UPDATE ON content_metadata FOR EACH
        ROW EXECUTE PROCEDURE trigger_fct_tu_content_metadata()

The query as it is takes approx. 35 seconds, which is very bad. If I
take out the line:

    LEFT JOIN product_metadata pm4 ON p.product_id = pm4.product_id AND
    pm4.name::text = 'product_title'::text

then the time is under 1 second. 

Here you can see the plan for the query (as it is here, i.e. when it
takes a lot of time): http://explain.depesz.com/s/K9s

As far as I can see, the wrong index is used. In the lines

    "->  Bitmap Heap Scan on product_metadata pm4 
    (cost=6014.11..257694.54 rows=579474 width=8) (actual
    time=282.364..13005.344 rows=557834 loops=1)"
        "Recheck Cond: ((name)::text = 'product_title'::text)"
        "Buffers: shared read=175851"
        "->  Bitmap Index Scan on idx_product_metadata_03 
        (cost=0.00..5869.24 rows=579474 width=0) (actual
        time=222.724..222.724 rows=557834 loops=1)"
            "Index Cond: ((name)::text = 'product_title'::text)"
            "Buffers: shared read=3953"

it can be seen that it uses idx_product_metadata_03 which is on (name,
value). Shouldn't it use index_product_metadata_02 which is on
(product_id, name)? 

Or is there another reason why this query is so slow? 

I have changed some of the default settings of Postgres as follows:

random_page_cost = 1.4
cpu_index_tuple_cost = 0.00001
effective_cache_size = 256MB
work_mem = 300MB

If you need any other information, let me know.

Thanks in advance!

With regards,
Stelian Iancu


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance