Обсуждение: 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
Stelian Iancu <stelian@iancu.ch> writes:
> 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:
> [ 13-way join joined to a 3-way join ]
Think you'll need to raise join_collapse_limit and from_collapse_limit
to get the best plan here. The planning time might hurt, though.
TBH that schema looks designed for inefficiency; you'd be better off
rethinking the design rather than hoping the planner is smart enough
to save you from it.
regards, tom lane
Hello Stelian,
Have you tried to use func_table module?, I think it will help you to eliminate all the joins.
Regards
On Monday, January 27, 2014 5:54 PM, Stelian Iancu <stelian@iancu.ch> wrote:
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
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
On Mon, Jan 27, 2014, at 9:20, salah jubeh wrote: > Hello Stelian, > Hello, > Have you tried to use func_table module?, I think it will help you to eliminate all the joins. No, I haven't. I can have a look later, thanks. > > Regards > >
On Mon, Jan 27, 2014, at 7:06, Tom Lane wrote: > Stelian Iancu <stelian@iancu.ch> writes: > > 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: > > [ 13-way join joined to a 3-way join ] > > Think you'll need to raise join_collapse_limit and from_collapse_limit > to get the best plan here. The planning time might hurt, though. > I did raise both to 40 and it works flawless (for now). I got the response time to less than a second. However I don't know what the implications are for the future. > TBH that schema looks designed for inefficiency; you'd be better off > rethinking the design rather than hoping the planner is smart enough > to save you from it. > Heh, I wish it was this easy. This whole thing is part of us moving away from Oracle to Postgres. We already have this huge DB with this schema in Oracle (which was successfully imported into Postgres, minus these performance issues we're seeing now) and I don't know how feasible it is to even start thinking about a redesign. But I appreciate your input regarding this. Maybe one of these days I will have success in convincing my boss to even start taking a look at the design of the DB (you know the saying "it works, don't fix it"). > regards, tom lane > > > -- > Sent via pgsql-performance mailing list > (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance
My developers have had the same issue. Postgres 9.2.3 on Linux 5.6. The query planner estimates (for 27 table join SQL) that using the nestloop is faster, when in fact it is not. A hashjoin returns results faster. We've set enable_nestloop = false and have gotten good results. The problem is, nestoop would be faster for other types of queries. Maybe ones with fewer joins. Recently we made a change that forced our multi join queires to slow down. We now build temp views for each user session. To speed these queries up, we up'd geqo_effort = 10. This has also given us good results; but again, we don't know if there will be another impact down the road. Same issue here with redesign. There is some simple denormalization we could do that would minimize our joins. Instead if link tables, we would utilize hstore, json or array columns types. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Slow-query-wrong-index-used-maybe-tp5788979p5789045.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
On 28/01/14 08:10, bobJobS wrote: > My developers have had the same issue. > > Postgres 9.2.3 on Linux 5.6. > The latest Linux kernel is 3.13 (https://www.kernel.org), so I assume 5.6 is a distribution version. So which distribution of Linux are you using? Cheers, Gavin
On Mon, Jan 27, 2014, at 11:43, Gavin Flower wrote: > On 28/01/14 08:10, bobJobS wrote: > > My developers have had the same issue. > > > > Postgres 9.2.3 on Linux 5.6. > > > The latest Linux kernel is 3.13 (https://www.kernel.org), so I assume > 5.6 is a distribution version. > > So which distribution of Linux are you using? > > I cannot reply for Bob, but we're on Debian 7. > Cheers, > Gavin > > > -- > Sent via pgsql-performance mailing list > (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance
RHEL 5.10 kernel 2.6.18 -- View this message in context: http://postgresql.1045698.n5.nabble.com/Slow-query-wrong-index-used-maybe-tp5788979p5789206.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.