Slow query (wrong index used maybe)

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