Re: [PERFORM] Slow query (wrong index used maybe)
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. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow query (wrong index used maybe)
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow query (wrong index used maybe)
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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow query (wrong index used maybe)
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. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow query (wrong index used maybe)
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 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, '99'::text) AS type_2_set_number, to_number(cm6.value::text, '99'::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 |
Re: [PERFORM] Slow query (wrong index used maybe)
On Mon, Jan 27, 2014, at 7:06, Tom Lane wrote: > Stelian Iancu 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow query (wrong index used maybe)
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 > > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow query (wrong index used maybe)
Stelian Iancu 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] 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, '99'::text) AS type_2_set_number, to_number(cm6.value::text, '99'::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) |
