Re: [HACKERS] [9.1] unusable for large views
Hello please, send a result of explain analyze on 9.1.1 and older please, use http://explain.depesz.com/ Regards Pavel Stehule 2011/10/24 Omar Bettin : > Hello, > > > > I have tried 9.1.1 win64 version and when I am trying to declare a cursor > for a very large view (lot of joins and aggregate functions), > > postgres is using around 3GB of memory and the query never returns. > > > > Same proble selecting from the view without cursor. > > > > Same query worked fine from 8.3.3 to 9.0.5. > > > > Should I change some configuration params to have the same behavior as > previous versions? > > > > > > > > Tried on Win2008 server R2 64bit 8GB RAM. > > also on Win7 64bit 8GB RAM. > > default postgresql.conf > > > > Regards, > > > > > > The view (!) > > > > CREATE OR REPLACE VIEW articoli_rip_ven20110227_view AS > > SELECT a.pk_id, a.articolo, a.articolo_f, a.origine, ( SELECT > ditte.attivita > > FROM ditte > > WHERE ditte.partitario = a.cod_fornit) AS dummy_1, a.descrizion, > a.taglia, a.peso_spec, a.giacenza_colli, a.giacenza, rim.colli AS > rimanenza_colli, rim.quantita AS rimanenza_qta, rimass.colli AS > rimanenza_ass_colli, rimass.quantita AS rimanenza_ass_qta, > COALESCE(b20.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b21.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b22.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b23.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b24.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b25.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b26.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b27.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b28.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b29.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b30.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b31.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b32.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b33.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b34.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b35.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b36.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b37.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b38.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b39.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b40.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b41.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b42.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b43.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b44.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b45.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b46.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b47.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b48.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b49.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b50.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b51.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b52.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b53.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b54.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b55.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b56.quantita, NULL::numeric, 0::numeric)::double precision + > (COALESCE(rim.quantita, NULL::numeric, 0::numeric) + > COALESCE(rimass.quantita, NULL::numeric, 0::numeric))::double precision - > (COALESCE(b1.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b2.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b3.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b4.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b5.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b6.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b7.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b8.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b9.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b10.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b11.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b12.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b13.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b14.quantita, NULL::numeric, 0::numeric)::double precision +
Re: [HACKERS] [9.1] unusable for large views (SOLVED)
2011/10/24 Omar Bettin : > ...sorry guys... > > was a bad configuration of database. > > 9.1.1 is working good. > > is 4% to 8% faster than 9.0.5. > > Thanks a lot. > > Regards > > Omar > > P.s. > attached EXPLAIN > attachment is missing Pavel > >>Hmm. A 59-table join is pretty enormous > > and is not the biggest, basically are delivery notes for one day seen in > vertical. > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
R: [HACKERS] [9.1] unusable for large views (SOLVED)
...sorry guys... was a bad configuration of database. 9.1.1 is working good. is 4% to 8% faster than 9.0.5. Thanks a lot. Regards Omar P.s. attached EXPLAIN >Hmm. A 59-table join is pretty enormous and is not the biggest, basically are delivery notes for one day seen in vertical. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [9.1] unusable for large views
On 24/10/11 10:57, Omar Bettin wrote: > > [monster query] I see that your problem is already solved, but incidentially I'm working on a join order planning module and I'm looking for real-life examples of humongous queries like that to benchmark against them. Any chance you could share the schema, or at least part of it, that goes with this query? Or perhaps you have more of these queries? Cheers, Jan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [9.1] unusable for large views
"Omar Bettin" writes: > I have tried 9.1.1 win64 version and when I am trying to declare a cursor > for a very large view (lot of joins and aggregate functions), > postgres is using around 3GB of memory and the query never returns. Could we see a self-contained test case? I'm not about to try to reverse-engineer the schema that goes with such a monster query. http://wiki.postgresql.org/wiki/Guide_to_reporting_problems regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
R: [HACKERS] [9.1] unusable for large views (SOLVED)
Hi Tom, ...are about two hours I am trying to communicate that the problem has been solved, but I do not see the messages in the mailing list... Anyway, the problems was a bad installation of database (pgsql functions). 9.1.1 is working good. is 4% to 8% faster than 9.0.5. Thanks a lot to everyone. Regards, Omar -Messaggio originale- Da: Tom Lane [mailto:t...@sss.pgh.pa.us] Inviato: lunedì 24 ottobre 2011 16:46 A: Omar Bettin Cc: pgsql-hackers@postgresql.org Oggetto: Re: [HACKERS] [9.1] unusable for large views "Omar Bettin" writes: > I have tried 9.1.1 win64 version and when I am trying to declare a cursor > for a very large view (lot of joins and aggregate functions), > postgres is using around 3GB of memory and the query never returns. Could we see a self-contained test case? I'm not about to try to reverse-engineer the schema that goes with such a monster query. http://wiki.postgresql.org/wiki/Guide_to_reporting_problems regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
R: [HACKERS] [9.1] unusable for large views (SOLVED)
...sorry guys... was a misconfiguration of database. 9.1.1 is working good. is ~4% faster than 9.0.5 for same query. Thanks a lot. Regards -Messaggio originale- Da: Pavel Stehule [mailto:pavel.steh...@gmail.com] Inviato: lunedì 24 ottobre 2011 12:13 A: Omar Bettin Cc: pgsql-hackers@postgresql.org Oggetto: Re: [HACKERS] [9.1] unusable for large views Hello please, send a result of explain analyze on 9.1.1 and older please, use http://explain.depesz.com/ Regards Pavel Stehule 2011/10/24 Omar Bettin : > Hello, > > > > I have tried 9.1.1 win64 version and when I am trying to declare a cursor > for a very large view (lot of joins and aggregate functions), > > postgres is using around 3GB of memory and the query never returns. > > > > Same proble selecting from the view without cursor. > > > > Same query worked fine from 8.3.3 to 9.0.5. > > > > Should I change some configuration params to have the same behavior as > previous versions? > > > > > > > > Tried on Win2008 server R2 64bit 8GB RAM. > > also on Win7 64bit 8GB RAM. > > default postgresql.conf > > > > Regards, > > > > > > The view (!) > > > > CREATE OR REPLACE VIEW articoli_rip_ven20110227_view AS > > SELECT a.pk_id, a.articolo, a.articolo_f, a.origine, ( SELECT > ditte.attivita > >FROM ditte > > WHERE ditte.partitario = a.cod_fornit) AS dummy_1, a.descrizion, > a.taglia, a.peso_spec, a.giacenza_colli, a.giacenza, rim.colli AS > rimanenza_colli, rim.quantita AS rimanenza_qta, rimass.colli AS > rimanenza_ass_colli, rimass.quantita AS rimanenza_ass_qta, > COALESCE(b20.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b21.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b22.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b23.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b24.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b25.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b26.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b27.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b28.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b29.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b30.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b31.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b32.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b33.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b34.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b35.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b36.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b37.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b38.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b39.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b40.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b41.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b42.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b43.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b44.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b45.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b46.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b47.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b48.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b49.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b50.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b51.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b52.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b53.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b54.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b55.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b56.quantita, NULL::numeric, 0::numeric)::double precision + > (COALESCE(rim.quantita, NULL::numeric, 0::numeric) + > COALESCE(rimass.quantita, NULL::numeric, 0::numeric))::double precision - > (COALESCE(b1.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b2.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b3.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b4.quantita, NULL::numeric, 0::numeric)::double precision + > COALESCE(b5.quantita, NULL::numeric,
Re: [HACKERS] [9.1] unusable for large views
On Mon, Oct 24, 2011 at 4:57 AM, Omar Bettin wrote: > I have tried 9.1.1 win64 version and when I am trying to declare a cursor > for a very large view (lot of joins and aggregate functions), > > postgres is using around 3GB of memory and the query never returns. Hmm. A 59-table join is pretty enormous. I wish we had a better way to handle these kinds of queries. Odds are good that the join order doesn't matter much, and in an ideal world we would be able to notice that and just use some simple heuristic to pick a tolerably good one. As it is, I am a bit surprised to hear that GEQO isn't bailing you out. Can you EXPLAIN a query against that view, or does even that wipe out? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
