[9.1] unusable for large views
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 +
COALESCE(b15.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b16.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b17.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b18.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b19.quantita, NULL::numeric, 0::numeric)::double precision) AS
diff_qta, COALESCE(b1.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b2.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b3.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b4.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b5.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b6.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b7.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b8.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b9.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b10.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b11.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b12.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b13.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b14.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b15.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b16.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b17.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b18.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b19.colli::integer, NULL::integer, 0)::smallint AS sum_colli_cli,
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 +
COALESCE(b15.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b16.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b17.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b18.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b19.quantita, NULL::numeric, 0::numeric)::double precision AS
sum_qta_cli, COALESCE(b20.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b21.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b22.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b23.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b24.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b25.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b26.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b27.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b28.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b29.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b30.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b31.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b32.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b33.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b34.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b35.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b36.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b37.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b38.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b39.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b40.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b41.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b42.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b43.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b44.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b45.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b46.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b47.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b48.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b49.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b50.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b51.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b52.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b53.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b54.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b55.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b56.colli::integer, NULL::integer, 0)::smallint +
(COALESCE(rim.colli, NULL::bigint, 0::bigint) + COALESCE(rimass.colli,
NULL::bigint, 0::bigint))::smallint AS sum_colli_for, 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 AS sum_qta_for, COALESCE(b20.colli::integer,
NULL::integer, 0)::smallint + COALESCE(b21.colli::integer, NULL::integer,
0)::smallint + COALESCE(b22.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b23.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b24.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b25.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b26.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b27.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b28.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b29.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b30.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b31.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b32.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b33.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b34.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b35.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b36.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b37.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b38.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b39.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b40.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b41.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b42.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b43.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b44.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b45.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b46.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b47.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b48.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b49.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b50.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b51.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b52.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b53.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b54.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b55.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b56.colli::integer, NULL::integer, 0)::smallint +
(COALESCE(rim.colli, NULL::bigint, 0::bigint) + COALESCE(rimass.colli,
NULL::bigint, 0::bigint))::smallint - (COALESCE(b1.colli::integer,
NULL::integer, 0)::smallint + COALESCE(b2.colli::integer, NULL::integer,
0)::smallint + COALESCE(b3.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b4.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b5.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b6.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b7.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b8.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b9.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b10.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b11.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b12.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b13.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b14.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b15.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b16.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b17.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b18.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b19.colli::integer, NULL::integer, 0)::smallint) AS diff_colli,
(COALESCE(b1.stato::integer, NULL::integer, 0)::smallint > 0 OR b1.articolo
IS NULL) AND (COALESCE(b2.stato::integer, NULL::integer, 0)::smallint > 0 OR
b2.articolo IS NULL) AND (COALESCE(b3.stato::integer, NULL::integer,
0)::smallint > 0 OR b3.articolo IS NULL) AND (COALESCE(b4.stato::integer,
NULL::integer, 0)::smallint > 0 OR b4.articolo IS NULL) AND
(COALESCE(b5.stato::integer, NULL::integer, 0)::smallint > 0 OR b5.articolo
IS NULL) AND (COALESCE(b6.stato::integer, NULL::integer, 0)::smallint > 0 OR
b6.articolo IS NULL) AND (COALESCE(b7.stato::integer, NULL::integer,
0)::smallint > 0 OR b7.articolo IS NULL) AND (COALESCE(b8.stato::integer,
NULL::integer, 0)::smallint > 0 OR b8.articolo IS NULL) AND
(COALESCE(b9.stato::integer, NULL::integer, 0)::smallint > 0 OR b9.articolo
IS NULL) AND (COALESCE(b10.stato::integer, NULL::integer, 0)::smallint > 0
OR b10.articolo IS NULL) AND (COALESCE(b11.stato::integer, NULL::integer,
0)::smallint > 0 OR b11.articolo IS NULL) AND (COALESCE(b12.stato::integer,
NULL::integer, 0)::smallint > 0 OR b12.articolo IS NULL) AND
(COALESCE(b13.stato::integer, NULL::integer, 0)::smallint > 0 OR
b13.articolo IS NULL) AND (COALESCE(b14.stato::integer, NULL::integer,
0)::smallint > 0 OR b14.articolo IS NULL) AND (COALESCE(b15.stato::integer,
NULL::integer, 0)::smallint > 0 OR b15.articolo IS NULL) AND
(COALESCE(b16.stato::integer, NULL::integer, 0)::smallint > 0 OR
b16.articolo IS NULL) AND (COALESCE(b17.stato::integer, NULL::integer,
0)::smallint > 0 OR b17.articolo IS NULL) AND (COALESCE(b18.stato::integer,
NULL::integer, 0)::smallint > 0 OR b18.articolo IS NULL) AND
(COALESCE(b19.stato::integer, NULL::integer, 0)::smallint > 0 OR
b19.articolo IS NULL) AS chk_cli, (COALESCE(b20.stato, NULL::integer,
0)::smallint > 0 OR b20.articolo IS NULL) AND (COALESCE(b21.stato,
NULL::integer, 0)::smallint > 0 OR b21.articolo IS NULL) AND
(COALESCE(b22.stato, NULL::integer, 0)::smallint > 0 OR b22.articolo IS
NULL) AND (COALESCE(b23.stato, NULL::integer, 0)::smallint > 0 OR
b23.articolo IS NULL) AND (COALESCE(b24.stato, NULL::integer, 0)::smallint >
0 OR b24.articolo IS NULL) AND (COALESCE(b25.stato, NULL::integer,
0)::smallint > 0 OR b25.articolo IS NULL) AND (COALESCE(b26.stato,
NULL::integer, 0)::smallint > 0 OR b26.articolo IS NULL) AND
(COALESCE(b27.stato, NULL::integer, 0)::smallint > 0 OR b27.articolo IS
NULL) AND (COALESCE(b28.stato, NULL::integer, 0)::smallint > 0 OR
b28.articolo IS NULL) AND (COALESCE(b29.stato, NULL::integer, 0)::smallint >
0 OR b29.articolo IS NULL) AND (COALESCE(b30.stato, NULL::integer,
0)::smallint > 0 OR b30.articolo IS NULL) AND (COALESCE(b31.stato,
NULL::integer, 0)::smallint > 0 OR b31.articolo IS NULL) AND
(COALESCE(b32.stato, NULL::integer, 0)::smallint > 0 OR b32.articolo IS
NULL) AND (COALESCE(b33.stato, NULL::integer, 0)::smallint > 0 OR
b33.articolo IS NULL) AND (COALESCE(b34.stato, NULL::integer, 0)::smallint >
0 OR b34.articolo IS NULL) AND (COALESCE(b35.stato, NULL::integer,
0)::smallint > 0 OR b35.articolo IS NULL) AND (COALESCE(b36.stato,
NULL::integer, 0)::smallint > 0 OR b36.articolo IS NULL) AND
(COALESCE(b37.stato, NULL::integer, 0)::smallint > 0 OR b37.articolo IS
NULL) AND (COALESCE(b38.stato, NULL::integer, 0)::smallint > 0 OR
b38.articolo IS NULL) AND (COALESCE(b39.stato, NULL::integer, 0)::smallint >
0 OR b39.articolo IS NULL) AND (COALESCE(b40.stato, NULL::integer,
0)::smallint > 0 OR b40.articolo IS NULL) AND (COALESCE(b41.stato,
NULL::integer, 0)::smallint > 0 OR b41.articolo IS NULL) AND
(COALESCE(b42.stato, NULL::integer, 0)::smallint > 0 OR b42.articolo IS
NULL) AND (COALESCE(b43.stato, NULL::integer, 0)::smallint > 0 OR
b43.articolo IS NULL) AND (COALESCE(b44.stato, NULL::integer, 0)::smallint >
0 OR b44.articolo IS NULL) AND (COALESCE(b45.stato, NULL::integer,
0)::smallint > 0 OR b45.articolo IS NULL) AND (COALESCE(b46.stato,
NULL::integer, 0)::smallint > 0 OR b46.articolo IS NULL) AND
(COALESCE(b47.stato, NULL::integer, 0)::smallint > 0 OR b47.articolo IS
NULL) AND (COALESCE(b48.stato, NULL::integer, 0)::smallint > 0 OR
b48.articolo IS NULL) AND (COALESCE(b49.stato, NULL::integer, 0)::smallint >
0 OR b49.articolo IS NULL) AND (COALESCE(b50.stato, NULL::integer,
0)::smallint > 0 OR b50.articolo IS NULL) AND (COALESCE(b51.stato,
NULL::integer, 0)::smallint > 0 OR b51.articolo IS NULL) AND
(COALESCE(b52.stato, NULL::integer, 0)::smallint > 0 OR b52.articolo IS
NULL) AND (COALESCE(b53.stato, NULL::integer, 0)::smallint > 0 OR
b53.articolo IS NULL) AND (COALESCE(b54.stato, NULL::integer, 0)::smallint >
0 OR b54.articolo IS NULL) AND (COALESCE(b55.stato, NULL::integer,
0)::smallint > 0 OR b55.articolo IS NULL) AND (COALESCE(b56.stato,
NULL::integer, 0)::smallint > 0 OR b56.articolo IS NULL) AS chk_for,
b1.colli AS t_colli7717cli, b1.quantita AS t_qta7717cli, b1.stato AS
st7717cli, b1.eti_ok AS ok7717cli, b2.colli AS t_colli7705cli, b2.quantita
AS t_qta7705cli, b2.stato AS st7705cli, b2.eti_ok AS ok7705cli, b3.colli AS
t_colli7715cli, b3.quantita AS t_qta7715cli, b3.stato AS st7715cli,
b3.eti_ok AS ok7715cli, b4.colli AS t_colli7704cli, b4.quantita AS
t_qta7704cli, b4.stato AS st7704cli, b4.eti_ok AS ok7704cli, b5.colli AS
t_colli7714cli, b5.quantita AS t_qta7714cli, b5.stato AS st7714cli,
b5.eti_ok AS ok7714cli, b6.colli AS t_colli7718cli, b6.quantita AS
t_qta7718cli, b6.stato AS st7718cli, b6.eti_ok AS ok7718cli, b7.colli AS
t_colli7713cli, b7.quantita AS t_qta7713cli, b7.stato AS st7713cli,
b7.eti_ok AS ok7713cli, b8.colli AS t_colli7708cli, b8.quantita AS
t_qta7708cli, b8.stato AS st7708cli, b8.eti_ok AS ok7708cli, b9.colli AS
t_colli7719cli, b9.quantita AS t_qta7719cli, b9.stato AS st7719cli,
b9.eti_ok AS ok7719cli, b10.colli AS t_colli7709cli, b10.quantita AS
t_qta7709cli, b10.stato AS st7709cli, b10.eti_ok AS ok7709cli, b11.colli AS
t_colli7722cli, b11.quantita AS t_qta7722cli, b11.stato AS st7722cli,
b11.eti_ok AS ok7722cli, b12.colli AS t_colli7706cli, b12.quantita AS
t_qta7706cli, b12.stato AS st7706cli, b12.eti_ok AS ok7706cli, b13.colli AS
t_colli7716cli, b13.quantita AS t_qta7716cli, b13.stato AS st7716cli,
b13.eti_ok AS ok7716cli, b14.colli AS t_colli7721cli, b14.quantita AS
t_qta7721cli, b14.stato AS st7721cli, b14.eti_ok AS ok7721cli, b15.colli AS
t_colli7707cli, b15.quantita AS t_qta7707cli, b15.stato AS st7707cli,
b15.eti_ok AS ok7707cli, b16.colli AS t_colli7712cli, b16.quantita AS
t_qta7712cli, b16.stato AS st7712cli, b16.eti_ok AS ok7712cli, b17.colli AS
t_colli7711cli, b17.quantita AS t_qta7711cli, b17.stato AS st7711cli,
b17.eti_ok AS ok7711cli, b18.colli AS t_colli7710cli, b18.quantita AS
t_qta7710cli, b18.stato AS st7710cli, b18.eti_ok AS ok7710cli, b19.colli AS
t_colli7720cli, b19.quantita AS t_qta7720cli, b19.stato AS st7720cli,
b19.eti_ok AS ok7720cli, b20.colli AS t_colli18499for, b20.quantita AS
t_qta18499for, b20.stato AS st18499for, b21.colli AS t_colli18502for,
b21.quantita AS t_qta18502for, b21.stato AS st18502for, b22.colli AS
t_colli18469for, b22.quantita AS t_qta18469for, b22.stato AS st18469for,
b23.colli AS t_colli18475for, b23.quantita AS t_qta18475for, b23.stato AS
st18475for, b24.colli AS t_colli18472for, b24.quantita AS t_qta18472for,
b24.stato AS st18472for, b25.colli AS t_colli18462for, b25.quantita AS
t_qta18462for, b25.stato AS st18462for, b26.colli AS t_colli18479for,
b26.quantita AS t_qta18479for, b26.stato AS st18479for, b27.colli AS
t_colli18505for, b27.quantita AS t_qta18505for, b27.stato AS st18505for,
b28.colli AS t_colli18470for, b28.quantita AS t_qta18470for, b28.stato AS
st18470for, b29.colli AS t_colli18477for, b29.quantita AS t_qta18477for,
b29.stato AS st18477for, b30.colli AS t_colli18465for, b30.quantita AS
t_qta18465for, b30.stato AS st18465for, b31.colli AS t_colli18453for,
b31.quantita AS t_qta18453for, b31.stato AS st18453for, b32.colli AS
t_colli18457for, b32.quantita AS t_qta18457for, b32.stato AS st18457for,
b33.colli AS t_colli18450for, b33.quantita AS t_qta18450for, b33.stato AS
st18450for, b34.colli AS t_colli18451for, b34.quantita AS t_qta18451for,
b34.stato AS st18451for, b35.colli AS t_colli18454for, b35.quantita AS
t_qta18454for, b35.stato AS st18454for, b36.colli AS t_colli18456for,
b36.quantita AS t_qta18456for, b36.stato AS st18456for, b37.colli AS
t_colli18455for, b37.quantita AS t_qta18455for, b37.stato AS st18455for,
b38.colli AS t_colli18458for, b38.quantita AS t_qta18458for, b38.stato AS
st18458for, b39.colli AS t_colli18501for, b39.quantita AS t_qta18501for,
b39.stato AS st18501for, b40.colli AS t_colli18504for, b40.quantita AS
t_qta18504for, b40.stato AS st18504for, b41.colli AS t_colli18471for,
b41.quantita AS t_qta18471for, b41.stato AS st18471for, b42.colli AS
t_colli18500for, b42.quantita AS t_qta18500for, b42.stato AS st18500for,
b43.colli AS t_colli18503for, b43.quantita AS t_qta18503for, b43.stato AS
st18503for, b44.colli AS t_colli18461for, b44.quantita AS t_qta18461for,
b44.stato AS st18461for, b45.colli AS t_colli18478for, b45.quantita AS
t_qta18478for, b45.stato AS st18478for, b46.colli AS t_colli18467for,
b46.quantita AS t_qta18467for, b46.stato AS st18467for, b47.colli AS
t_colli18474for, b47.quantita AS t_qta18474for, b47.stato AS st18474for,
b48.colli AS t_colli18459for, b48.quantita AS t_qta18459for, b48.stato AS
st18459for, b49.colli AS t_colli18460for, b49.quantita AS t_qta18460for,
b49.stato AS st18460for, b50.colli AS t_colli18464for, b50.quantita AS
t_qta18464for, b50.stato AS st18464for, b51.colli AS t_colli18480for,
b51.quantita AS t_qta18480for, b51.stato AS st18480for, b52.colli AS
t_colli18506for, b52.quantita AS t_qta18506for, b52.stato AS st18506for,
b53.colli AS t_colli18452for, b53.quantita AS t_qta18452for, b53.stato AS
st18452for, b54.colli AS t_colli18463for, b54.quantita AS t_qta18463for,
b54.stato AS st18463for, b55.colli AS t_colli18466for, b55.quantita AS
t_qta18466for, b55.stato AS st18466for, b56.colli AS t_colli18473for,
b56.quantita AS t_qta18473for, b56.stato AS st18473for
FROM articoli a
LEFT JOIN articoli_rim_rip_ven20110227_view rim ON rim.articolo =
a.articolo
LEFT JOIN articoli_rim_ass_rip_ven20110227_view rimass ON rimass.articolo
= a.articolo
LEFT JOIN bolrighe b1 ON b1.numero = 7717 AND b1.articolo = a.articolo
LEFT JOIN bolrighe b2 ON b2.numero = 7705 AND b2.articolo = a.articolo
LEFT JOIN bolrighe b3 ON b3.numero = 7715 AND b3.articolo = a.articolo
LEFT JOIN bolrighe b4 ON b4.numero = 7704 AND b4.articolo = a.articolo
LEFT JOIN bolrighe b5 ON b5.numero = 7714 AND b5.articolo = a.articolo
LEFT JOIN bolrighe b6 ON b6.numero = 7718 AND b6.articolo = a.articolo
LEFT JOIN bolrighe b7 ON b7.numero = 7713 AND b7.articolo = a.articolo
LEFT JOIN bolrighe b8 ON b8.numero = 7708 AND b8.articolo = a.articolo
LEFT JOIN bolrighe b9 ON b9.numero = 7719 AND b9.articolo = a.articolo
LEFT JOIN bolrighe b10 ON b10.numero = 7709 AND b10.articolo = a.articolo
LEFT JOIN bolrighe b11 ON b11.numero = 7722 AND b11.articolo = a.articolo
LEFT JOIN bolrighe b12 ON b12.numero = 7706 AND b12.articolo = a.articolo
LEFT JOIN bolrighe b13 ON b13.numero = 7716 AND b13.articolo = a.articolo
LEFT JOIN bolrighe b14 ON b14.numero = 7721 AND b14.articolo = a.articolo
LEFT JOIN bolrighe b15 ON b15.numero = 7707 AND b15.articolo = a.articolo
LEFT JOIN bolrighe b16 ON b16.numero = 7712 AND b16.articolo = a.articolo
LEFT JOIN bolrighe b17 ON b17.numero = 7711 AND b17.articolo = a.articolo
LEFT JOIN bolrighe b18 ON b18.numero = 7710 AND b18.articolo = a.articolo
LEFT JOIN bolrighe b19 ON b19.numero = 7720 AND b19.articolo = a.articolo
LEFT JOIN magmodet b20 ON b20.numero = 18499 AND b20.articolo =
a.articolo
LEFT JOIN magmodet b21 ON b21.numero = 18502 AND b21.articolo =
a.articolo
LEFT JOIN magmodet b22 ON b22.numero = 18469 AND b22.articolo =
a.articolo
LEFT JOIN magmodet b23 ON b23.numero = 18475 AND b23.articolo =
a.articolo
LEFT JOIN magmodet b24 ON b24.numero = 18472 AND b24.articolo =
a.articolo
LEFT JOIN magmodet b25 ON b25.numero = 18462 AND b25.articolo =
a.articolo
LEFT JOIN magmodet b26 ON b26.numero = 18479 AND b26.articolo =
a.articolo
LEFT JOIN magmodet b27 ON b27.numero = 18505 AND b27.articolo =
a.articolo
LEFT JOIN magmodet b28 ON b28.numero = 18470 AND b28.articolo =
a.articolo
LEFT JOIN magmodet b29 ON b29.numero = 18477 AND b29.articolo =
a.articolo
LEFT JOIN magmodet b30 ON b30.numero = 18465 AND b30.articolo =
a.articolo
LEFT JOIN magmodet b31 ON b31.numero = 18453 AND b31.articolo =
a.articolo
LEFT JOIN magmodet b32 ON b32.numero = 18457 AND b32.articolo =
a.articolo
LEFT JOIN magmodet b33 ON b33.numero = 18450 AND b33.articolo =
a.articolo
LEFT JOIN magmodet b34 ON b34.numero = 18451 AND b34.articolo =
a.articolo
LEFT JOIN magmodet b35 ON b35.numero = 18454 AND b35.articolo =
a.articolo
LEFT JOIN magmodet b36 ON b36.numero = 18456 AND b36.articolo =
a.articolo
LEFT JOIN magmodet b37 ON b37.numero = 18455 AND b37.articolo =
a.articolo
LEFT JOIN magmodet b38 ON b38.numero = 18458 AND b38.articolo =
a.articolo
LEFT JOIN magmodet b39 ON b39.numero = 18501 AND b39.articolo =
a.articolo
LEFT JOIN magmodet b40 ON b40.numero = 18504 AND b40.articolo =
a.articolo
LEFT JOIN magmodet b41 ON b41.numero = 18471 AND b41.articolo =
a.articolo
LEFT JOIN magmodet b42 ON b42.numero = 18500 AND b42.articolo =
a.articolo
LEFT JOIN magmodet b43 ON b43.numero = 18503 AND b43.articolo =
a.articolo
LEFT JOIN magmodet b44 ON b44.numero = 18461 AND b44.articolo =
a.articolo
LEFT JOIN magmodet b45 ON b45.numero = 18478 AND b45.articolo =
a.articolo
LEFT JOIN magmodet b46 ON b46.numero = 18467 AND b46.articolo =
a.articolo
LEFT JOIN magmodet b47 ON b47.numero = 18474 AND b47.articolo =
a.articolo
LEFT JOIN magmodet b48 ON b48.numero = 18459 AND b48.articolo =
a.articolo
LEFT JOIN magmodet b49 ON b49.numero = 18460 AND b49.articolo =
a.articolo
LEFT JOIN magmodet b50 ON b50.numero = 18464 AND b50.articolo =
a.articolo
LEFT JOIN magmodet b51 ON b51.numero = 18480 AND b51.articolo =
a.articolo
LEFT JOIN magmodet b52 ON b52.numero = 18506 AND b52.articolo =
a.articolo
LEFT JOIN magmodet b53 ON b53.numero = 18452 AND b53.articolo =
a.articolo
LEFT JOIN magmodet b54 ON b54.numero = 18463 AND b54.articolo =
a.articolo
LEFT JOIN magmodet b55 ON b55.numero = 18466 AND b55.articolo =
a.articolo
LEFT JOIN magmodet b56 ON b56.numero = 18473 AND b56.articolo =
a.articolo;
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 <o.bettin@informaticaindustriale.it>:
Show quoted text
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.attivitaFROM 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 +
COALESCE(b15.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b16.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b17.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b18.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b19.quantita, NULL::numeric, 0::numeric)::double precision) AS
diff_qta, COALESCE(b1.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b2.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b3.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b4.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b5.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b6.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b7.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b8.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b9.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b10.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b11.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b12.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b13.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b14.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b15.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b16.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b17.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b18.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b19.colli::integer, NULL::integer, 0)::smallint AS sum_colli_cli,
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 +
COALESCE(b15.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b16.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b17.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b18.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b19.quantita, NULL::numeric, 0::numeric)::double precision AS
sum_qta_cli, COALESCE(b20.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b21.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b22.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b23.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b24.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b25.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b26.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b27.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b28.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b29.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b30.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b31.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b32.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b33.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b34.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b35.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b36.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b37.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b38.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b39.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b40.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b41.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b42.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b43.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b44.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b45.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b46.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b47.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b48.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b49.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b50.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b51.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b52.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b53.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b54.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b55.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b56.colli::integer, NULL::integer, 0)::smallint +
(COALESCE(rim.colli, NULL::bigint, 0::bigint) + COALESCE(rimass.colli,
NULL::bigint, 0::bigint))::smallint AS sum_colli_for, 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 AS sum_qta_for, COALESCE(b20.colli::integer,
NULL::integer, 0)::smallint + COALESCE(b21.colli::integer, NULL::integer,
0)::smallint + COALESCE(b22.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b23.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b24.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b25.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b26.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b27.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b28.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b29.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b30.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b31.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b32.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b33.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b34.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b35.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b36.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b37.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b38.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b39.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b40.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b41.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b42.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b43.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b44.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b45.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b46.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b47.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b48.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b49.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b50.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b51.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b52.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b53.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b54.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b55.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b56.colli::integer, NULL::integer, 0)::smallint +
(COALESCE(rim.colli, NULL::bigint, 0::bigint) + COALESCE(rimass.colli,
NULL::bigint, 0::bigint))::smallint - (COALESCE(b1.colli::integer,
NULL::integer, 0)::smallint + COALESCE(b2.colli::integer, NULL::integer,
0)::smallint + COALESCE(b3.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b4.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b5.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b6.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b7.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b8.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b9.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b10.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b11.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b12.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b13.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b14.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b15.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b16.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b17.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b18.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b19.colli::integer, NULL::integer, 0)::smallint) AS diff_colli,
(COALESCE(b1.stato::integer, NULL::integer, 0)::smallint > 0 OR b1.articolo
IS NULL) AND (COALESCE(b2.stato::integer, NULL::integer, 0)::smallint > 0 OR
b2.articolo IS NULL) AND (COALESCE(b3.stato::integer, NULL::integer,
0)::smallint > 0 OR b3.articolo IS NULL) AND (COALESCE(b4.stato::integer,
NULL::integer, 0)::smallint > 0 OR b4.articolo IS NULL) AND
(COALESCE(b5.stato::integer, NULL::integer, 0)::smallint > 0 OR b5.articolo
IS NULL) AND (COALESCE(b6.stato::integer, NULL::integer, 0)::smallint > 0 OR
b6.articolo IS NULL) AND (COALESCE(b7.stato::integer, NULL::integer,
0)::smallint > 0 OR b7.articolo IS NULL) AND (COALESCE(b8.stato::integer,
NULL::integer, 0)::smallint > 0 OR b8.articolo IS NULL) AND
(COALESCE(b9.stato::integer, NULL::integer, 0)::smallint > 0 OR b9.articolo
IS NULL) AND (COALESCE(b10.stato::integer, NULL::integer, 0)::smallint > 0
OR b10.articolo IS NULL) AND (COALESCE(b11.stato::integer, NULL::integer,
0)::smallint > 0 OR b11.articolo IS NULL) AND (COALESCE(b12.stato::integer,
NULL::integer, 0)::smallint > 0 OR b12.articolo IS NULL) AND
(COALESCE(b13.stato::integer, NULL::integer, 0)::smallint > 0 OR
b13.articolo IS NULL) AND (COALESCE(b14.stato::integer, NULL::integer,
0)::smallint > 0 OR b14.articolo IS NULL) AND (COALESCE(b15.stato::integer,
NULL::integer, 0)::smallint > 0 OR b15.articolo IS NULL) AND
(COALESCE(b16.stato::integer, NULL::integer, 0)::smallint > 0 OR
b16.articolo IS NULL) AND (COALESCE(b17.stato::integer, NULL::integer,
0)::smallint > 0 OR b17.articolo IS NULL) AND (COALESCE(b18.stato::integer,
NULL::integer, 0)::smallint > 0 OR b18.articolo IS NULL) AND
(COALESCE(b19.stato::integer, NULL::integer, 0)::smallint > 0 OR
b19.articolo IS NULL) AS chk_cli, (COALESCE(b20.stato, NULL::integer,
0)::smallint > 0 OR b20.articolo IS NULL) AND (COALESCE(b21.stato,
NULL::integer, 0)::smallint > 0 OR b21.articolo IS NULL) AND
(COALESCE(b22.stato, NULL::integer, 0)::smallint > 0 OR b22.articolo IS
NULL) AND (COALESCE(b23.stato, NULL::integer, 0)::smallint > 0 OR
b23.articolo IS NULL) AND (COALESCE(b24.stato, NULL::integer, 0)::smallint >
0 OR b24.articolo IS NULL) AND (COALESCE(b25.stato, NULL::integer,
0)::smallint > 0 OR b25.articolo IS NULL) AND (COALESCE(b26.stato,
NULL::integer, 0)::smallint > 0 OR b26.articolo IS NULL) AND
(COALESCE(b27.stato, NULL::integer, 0)::smallint > 0 OR b27.articolo IS
NULL) AND (COALESCE(b28.stato, NULL::integer, 0)::smallint > 0 OR
b28.articolo IS NULL) AND (COALESCE(b29.stato, NULL::integer, 0)::smallint >
0 OR b29.articolo IS NULL) AND (COALESCE(b30.stato, NULL::integer,
0)::smallint > 0 OR b30.articolo IS NULL) AND (COALESCE(b31.stato,
NULL::integer, 0)::smallint > 0 OR b31.articolo IS NULL) AND
(COALESCE(b32.stato, NULL::integer, 0)::smallint > 0 OR b32.articolo IS
NULL) AND (COALESCE(b33.stato, NULL::integer, 0)::smallint > 0 OR
b33.articolo IS NULL) AND (COALESCE(b34.stato, NULL::integer, 0)::smallint >
0 OR b34.articolo IS NULL) AND (COALESCE(b35.stato, NULL::integer,
0)::smallint > 0 OR b35.articolo IS NULL) AND (COALESCE(b36.stato,
NULL::integer, 0)::smallint > 0 OR b36.articolo IS NULL) AND
(COALESCE(b37.stato, NULL::integer, 0)::smallint > 0 OR b37.articolo IS
NULL) AND (COALESCE(b38.stato, NULL::integer, 0)::smallint > 0 OR
b38.articolo IS NULL) AND (COALESCE(b39.stato, NULL::integer, 0)::smallint >
0 OR b39.articolo IS NULL) AND (COALESCE(b40.stato, NULL::integer,
0)::smallint > 0 OR b40.articolo IS NULL) AND (COALESCE(b41.stato,
NULL::integer, 0)::smallint > 0 OR b41.articolo IS NULL) AND
(COALESCE(b42.stato, NULL::integer, 0)::smallint > 0 OR b42.articolo IS
NULL) AND (COALESCE(b43.stato, NULL::integer, 0)::smallint > 0 OR
b43.articolo IS NULL) AND (COALESCE(b44.stato, NULL::integer, 0)::smallint >
0 OR b44.articolo IS NULL) AND (COALESCE(b45.stato, NULL::integer,
0)::smallint > 0 OR b45.articolo IS NULL) AND (COALESCE(b46.stato,
NULL::integer, 0)::smallint > 0 OR b46.articolo IS NULL) AND
(COALESCE(b47.stato, NULL::integer, 0)::smallint > 0 OR b47.articolo IS
NULL) AND (COALESCE(b48.stato, NULL::integer, 0)::smallint > 0 OR
b48.articolo IS NULL) AND (COALESCE(b49.stato, NULL::integer, 0)::smallint >
0 OR b49.articolo IS NULL) AND (COALESCE(b50.stato, NULL::integer,
0)::smallint > 0 OR b50.articolo IS NULL) AND (COALESCE(b51.stato,
NULL::integer, 0)::smallint > 0 OR b51.articolo IS NULL) AND
(COALESCE(b52.stato, NULL::integer, 0)::smallint > 0 OR b52.articolo IS
NULL) AND (COALESCE(b53.stato, NULL::integer, 0)::smallint > 0 OR
b53.articolo IS NULL) AND (COALESCE(b54.stato, NULL::integer, 0)::smallint >
0 OR b54.articolo IS NULL) AND (COALESCE(b55.stato, NULL::integer,
0)::smallint > 0 OR b55.articolo IS NULL) AND (COALESCE(b56.stato,
NULL::integer, 0)::smallint > 0 OR b56.articolo IS NULL) AS chk_for,
b1.colli AS t_colli7717cli, b1.quantita AS t_qta7717cli, b1.stato AS
st7717cli, b1.eti_ok AS ok7717cli, b2.colli AS t_colli7705cli, b2.quantita
AS t_qta7705cli, b2.stato AS st7705cli, b2.eti_ok AS ok7705cli, b3.colli AS
t_colli7715cli, b3.quantita AS t_qta7715cli, b3.stato AS st7715cli,
b3.eti_ok AS ok7715cli, b4.colli AS t_colli7704cli, b4.quantita AS
t_qta7704cli, b4.stato AS st7704cli, b4.eti_ok AS ok7704cli, b5.colli AS
t_colli7714cli, b5.quantita AS t_qta7714cli, b5.stato AS st7714cli,
b5.eti_ok AS ok7714cli, b6.colli AS t_colli7718cli, b6.quantita AS
t_qta7718cli, b6.stato AS st7718cli, b6.eti_ok AS ok7718cli, b7.colli AS
t_colli7713cli, b7.quantita AS t_qta7713cli, b7.stato AS st7713cli,
b7.eti_ok AS ok7713cli, b8.colli AS t_colli7708cli, b8.quantita AS
t_qta7708cli, b8.stato AS st7708cli, b8.eti_ok AS ok7708cli, b9.colli AS
t_colli7719cli, b9.quantita AS t_qta7719cli, b9.stato AS st7719cli,
b9.eti_ok AS ok7719cli, b10.colli AS t_colli7709cli, b10.quantita AS
t_qta7709cli, b10.stato AS st7709cli, b10.eti_ok AS ok7709cli, b11.colli AS
t_colli7722cli, b11.quantita AS t_qta7722cli, b11.stato AS st7722cli,
b11.eti_ok AS ok7722cli, b12.colli AS t_colli7706cli, b12.quantita AS
t_qta7706cli, b12.stato AS st7706cli, b12.eti_ok AS ok7706cli, b13.colli AS
t_colli7716cli, b13.quantita AS t_qta7716cli, b13.stato AS st7716cli,
b13.eti_ok AS ok7716cli, b14.colli AS t_colli7721cli, b14.quantita AS
t_qta7721cli, b14.stato AS st7721cli, b14.eti_ok AS ok7721cli, b15.colli AS
t_colli7707cli, b15.quantita AS t_qta7707cli, b15.stato AS st7707cli,
b15.eti_ok AS ok7707cli, b16.colli AS t_colli7712cli, b16.quantita AS
t_qta7712cli, b16.stato AS st7712cli, b16.eti_ok AS ok7712cli, b17.colli AS
t_colli7711cli, b17.quantita AS t_qta7711cli, b17.stato AS st7711cli,
b17.eti_ok AS ok7711cli, b18.colli AS t_colli7710cli, b18.quantita AS
t_qta7710cli, b18.stato AS st7710cli, b18.eti_ok AS ok7710cli, b19.colli AS
t_colli7720cli, b19.quantita AS t_qta7720cli, b19.stato AS st7720cli,
b19.eti_ok AS ok7720cli, b20.colli AS t_colli18499for, b20.quantita AS
t_qta18499for, b20.stato AS st18499for, b21.colli AS t_colli18502for,
b21.quantita AS t_qta18502for, b21.stato AS st18502for, b22.colli AS
t_colli18469for, b22.quantita AS t_qta18469for, b22.stato AS st18469for,
b23.colli AS t_colli18475for, b23.quantita AS t_qta18475for, b23.stato AS
st18475for, b24.colli AS t_colli18472for, b24.quantita AS t_qta18472for,
b24.stato AS st18472for, b25.colli AS t_colli18462for, b25.quantita AS
t_qta18462for, b25.stato AS st18462for, b26.colli AS t_colli18479for,
b26.quantita AS t_qta18479for, b26.stato AS st18479for, b27.colli AS
t_colli18505for, b27.quantita AS t_qta18505for, b27.stato AS st18505for,
b28.colli AS t_colli18470for, b28.quantita AS t_qta18470for, b28.stato AS
st18470for, b29.colli AS t_colli18477for, b29.quantita AS t_qta18477for,
b29.stato AS st18477for, b30.colli AS t_colli18465for, b30.quantita AS
t_qta18465for, b30.stato AS st18465for, b31.colli AS t_colli18453for,
b31.quantita AS t_qta18453for, b31.stato AS st18453for, b32.colli AS
t_colli18457for, b32.quantita AS t_qta18457for, b32.stato AS st18457for,
b33.colli AS t_colli18450for, b33.quantita AS t_qta18450for, b33.stato AS
st18450for, b34.colli AS t_colli18451for, b34.quantita AS t_qta18451for,
b34.stato AS st18451for, b35.colli AS t_colli18454for, b35.quantita AS
t_qta18454for, b35.stato AS st18454for, b36.colli AS t_colli18456for,
b36.quantita AS t_qta18456for, b36.stato AS st18456for, b37.colli AS
t_colli18455for, b37.quantita AS t_qta18455for, b37.stato AS st18455for,
b38.colli AS t_colli18458for, b38.quantita AS t_qta18458for, b38.stato AS
st18458for, b39.colli AS t_colli18501for, b39.quantita AS t_qta18501for,
b39.stato AS st18501for, b40.colli AS t_colli18504for, b40.quantita AS
t_qta18504for, b40.stato AS st18504for, b41.colli AS t_colli18471for,
b41.quantita AS t_qta18471for, b41.stato AS st18471for, b42.colli AS
t_colli18500for, b42.quantita AS t_qta18500for, b42.stato AS st18500for,
b43.colli AS t_colli18503for, b43.quantita AS t_qta18503for, b43.stato AS
st18503for, b44.colli AS t_colli18461for, b44.quantita AS t_qta18461for,
b44.stato AS st18461for, b45.colli AS t_colli18478for, b45.quantita AS
t_qta18478for, b45.stato AS st18478for, b46.colli AS t_colli18467for,
b46.quantita AS t_qta18467for, b46.stato AS st18467for, b47.colli AS
t_colli18474for, b47.quantita AS t_qta18474for, b47.stato AS st18474for,
b48.colli AS t_colli18459for, b48.quantita AS t_qta18459for, b48.stato AS
st18459for, b49.colli AS t_colli18460for, b49.quantita AS t_qta18460for,
b49.stato AS st18460for, b50.colli AS t_colli18464for, b50.quantita AS
t_qta18464for, b50.stato AS st18464for, b51.colli AS t_colli18480for,
b51.quantita AS t_qta18480for, b51.stato AS st18480for, b52.colli AS
t_colli18506for, b52.quantita AS t_qta18506for, b52.stato AS st18506for,
b53.colli AS t_colli18452for, b53.quantita AS t_qta18452for, b53.stato AS
st18452for, b54.colli AS t_colli18463for, b54.quantita AS t_qta18463for,
b54.stato AS st18463for, b55.colli AS t_colli18466for, b55.quantita AS
t_qta18466for, b55.stato AS st18466for, b56.colli AS t_colli18473for,
b56.quantita AS t_qta18473for, b56.stato AS st18473forFROM articoli a
LEFT JOIN articoli_rim_rip_ven20110227_view rim ON rim.articolo =
a.articoloLEFT JOIN articoli_rim_ass_rip_ven20110227_view rimass ON rimass.articolo
= a.articoloLEFT JOIN bolrighe b1 ON b1.numero = 7717 AND b1.articolo = a.articolo
LEFT JOIN bolrighe b2 ON b2.numero = 7705 AND b2.articolo = a.articolo
LEFT JOIN bolrighe b3 ON b3.numero = 7715 AND b3.articolo = a.articolo
LEFT JOIN bolrighe b4 ON b4.numero = 7704 AND b4.articolo = a.articolo
LEFT JOIN bolrighe b5 ON b5.numero = 7714 AND b5.articolo = a.articolo
LEFT JOIN bolrighe b6 ON b6.numero = 7718 AND b6.articolo = a.articolo
LEFT JOIN bolrighe b7 ON b7.numero = 7713 AND b7.articolo = a.articolo
LEFT JOIN bolrighe b8 ON b8.numero = 7708 AND b8.articolo = a.articolo
LEFT JOIN bolrighe b9 ON b9.numero = 7719 AND b9.articolo = a.articolo
LEFT JOIN bolrighe b10 ON b10.numero = 7709 AND b10.articolo = a.articolo
LEFT JOIN bolrighe b11 ON b11.numero = 7722 AND b11.articolo = a.articolo
LEFT JOIN bolrighe b12 ON b12.numero = 7706 AND b12.articolo = a.articolo
LEFT JOIN bolrighe b13 ON b13.numero = 7716 AND b13.articolo = a.articolo
LEFT JOIN bolrighe b14 ON b14.numero = 7721 AND b14.articolo = a.articolo
LEFT JOIN bolrighe b15 ON b15.numero = 7707 AND b15.articolo = a.articolo
LEFT JOIN bolrighe b16 ON b16.numero = 7712 AND b16.articolo = a.articolo
LEFT JOIN bolrighe b17 ON b17.numero = 7711 AND b17.articolo = a.articolo
LEFT JOIN bolrighe b18 ON b18.numero = 7710 AND b18.articolo = a.articolo
LEFT JOIN bolrighe b19 ON b19.numero = 7720 AND b19.articolo = a.articolo
LEFT JOIN magmodet b20 ON b20.numero = 18499 AND b20.articolo =
a.articoloLEFT JOIN magmodet b21 ON b21.numero = 18502 AND b21.articolo =
a.articoloLEFT JOIN magmodet b22 ON b22.numero = 18469 AND b22.articolo =
a.articoloLEFT JOIN magmodet b23 ON b23.numero = 18475 AND b23.articolo =
a.articoloLEFT JOIN magmodet b24 ON b24.numero = 18472 AND b24.articolo =
a.articoloLEFT JOIN magmodet b25 ON b25.numero = 18462 AND b25.articolo =
a.articoloLEFT JOIN magmodet b26 ON b26.numero = 18479 AND b26.articolo =
a.articoloLEFT JOIN magmodet b27 ON b27.numero = 18505 AND b27.articolo =
a.articoloLEFT JOIN magmodet b28 ON b28.numero = 18470 AND b28.articolo =
a.articoloLEFT JOIN magmodet b29 ON b29.numero = 18477 AND b29.articolo =
a.articoloLEFT JOIN magmodet b30 ON b30.numero = 18465 AND b30.articolo =
a.articoloLEFT JOIN magmodet b31 ON b31.numero = 18453 AND b31.articolo =
a.articoloLEFT JOIN magmodet b32 ON b32.numero = 18457 AND b32.articolo =
a.articoloLEFT JOIN magmodet b33 ON b33.numero = 18450 AND b33.articolo =
a.articoloLEFT JOIN magmodet b34 ON b34.numero = 18451 AND b34.articolo =
a.articoloLEFT JOIN magmodet b35 ON b35.numero = 18454 AND b35.articolo =
a.articoloLEFT JOIN magmodet b36 ON b36.numero = 18456 AND b36.articolo =
a.articoloLEFT JOIN magmodet b37 ON b37.numero = 18455 AND b37.articolo =
a.articoloLEFT JOIN magmodet b38 ON b38.numero = 18458 AND b38.articolo =
a.articoloLEFT JOIN magmodet b39 ON b39.numero = 18501 AND b39.articolo =
a.articoloLEFT JOIN magmodet b40 ON b40.numero = 18504 AND b40.articolo =
a.articoloLEFT JOIN magmodet b41 ON b41.numero = 18471 AND b41.articolo =
a.articoloLEFT JOIN magmodet b42 ON b42.numero = 18500 AND b42.articolo =
a.articoloLEFT JOIN magmodet b43 ON b43.numero = 18503 AND b43.articolo =
a.articoloLEFT JOIN magmodet b44 ON b44.numero = 18461 AND b44.articolo =
a.articoloLEFT JOIN magmodet b45 ON b45.numero = 18478 AND b45.articolo =
a.articoloLEFT JOIN magmodet b46 ON b46.numero = 18467 AND b46.articolo =
a.articoloLEFT JOIN magmodet b47 ON b47.numero = 18474 AND b47.articolo =
a.articoloLEFT JOIN magmodet b48 ON b48.numero = 18459 AND b48.articolo =
a.articoloLEFT JOIN magmodet b49 ON b49.numero = 18460 AND b49.articolo =
a.articoloLEFT JOIN magmodet b50 ON b50.numero = 18464 AND b50.articolo =
a.articoloLEFT JOIN magmodet b51 ON b51.numero = 18480 AND b51.articolo =
a.articoloLEFT JOIN magmodet b52 ON b52.numero = 18506 AND b52.articolo =
a.articoloLEFT JOIN magmodet b53 ON b53.numero = 18452 AND b53.articolo =
a.articoloLEFT JOIN magmodet b54 ON b54.numero = 18463 AND b54.articolo =
a.articoloLEFT JOIN magmodet b55 ON b55.numero = 18466 AND b55.articolo =
a.articoloLEFT JOIN magmodet b56 ON b56.numero = 18473 AND b56.articolo =
a.articolo;
Import Notes
Reply to msg id not found: 4ea52d61.21c5ec0a.6579.fffffd26SMTPIN_ADDED@mx.google.comReference msg id not found: 4ea52d61.21c5ec0a.6579.fffffd26SMTPIN_ADDED@mx.google.com | Resolved by subject fallback
2011/10/24 Omar Bettin <o.bettin@informaticaindustriale.it>
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 +
COALESCE(b15.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b16.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b17.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b18.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b19.quantita, NULL::numeric, 0::numeric)::double precision) AS
diff_qta, COALESCE(b1.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b2.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b3.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b4.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b5.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b6.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b7.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b8.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b9.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b10.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b11.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b12.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b13.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b14.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b15.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b16.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b17.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b18.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b19.colli::integer, NULL::integer, 0)::smallint AS sum_colli_cli,
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 +
COALESCE(b15.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b16.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b17.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b18.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b19.quantita, NULL::numeric, 0::numeric)::double precision AS
sum_qta_cli, COALESCE(b20.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b21.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b22.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b23.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b24.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b25.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b26.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b27.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b28.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b29.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b30.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b31.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b32.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b33.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b34.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b35.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b36.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b37.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b38.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b39.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b40.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b41.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b42.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b43.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b44.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b45.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b46.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b47.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b48.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b49.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b50.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b51.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b52.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b53.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b54.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b55.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b56.colli::integer, NULL::integer, 0)::smallint +
(COALESCE(rim.colli, NULL::bigint, 0::bigint) + COALESCE(rimass.colli,
NULL::bigint, 0::bigint))::smallint AS sum_colli_for, 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 AS sum_qta_for, COALESCE(b20.colli::integer,
NULL::integer, 0)::smallint + COALESCE(b21.colli::integer, NULL::integer,
0)::smallint + COALESCE(b22.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b23.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b24.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b25.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b26.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b27.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b28.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b29.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b30.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b31.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b32.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b33.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b34.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b35.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b36.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b37.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b38.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b39.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b40.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b41.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b42.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b43.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b44.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b45.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b46.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b47.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b48.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b49.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b50.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b51.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b52.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b53.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b54.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b55.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b56.colli::integer, NULL::integer, 0)::smallint +
(COALESCE(rim.colli, NULL::bigint, 0::bigint) + COALESCE(rimass.colli,
NULL::bigint, 0::bigint))::smallint - (COALESCE(b1.colli::integer,
NULL::integer, 0)::smallint + COALESCE(b2.colli::integer, NULL::integer,
0)::smallint + COALESCE(b3.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b4.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b5.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b6.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b7.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b8.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b9.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b10.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b11.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b12.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b13.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b14.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b15.colli::integer, NULL::integer, 0)::smallint + COALESCE(b16.colli::integer,
NULL::integer, 0)::smallint + COALESCE(b17.colli::integer, NULL::integer,
0)::smallint + COALESCE(b18.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b19.colli::integer, NULL::integer, 0)::smallint) AS diff_colli,
(COALESCE(b1.stato::integer, NULL::integer, 0)::smallint > 0 OR b1.articolo
IS NULL) AND (COALESCE(b2.stato::integer, NULL::integer, 0)::smallint > 0 OR
b2.articolo IS NULL) AND (COALESCE(b3.stato::integer, NULL::integer,
0)::smallint > 0 OR b3.articolo IS NULL) AND (COALESCE(b4.stato::integer,
NULL::integer, 0)::smallint > 0 OR b4.articolo IS NULL) AND
(COALESCE(b5.stato::integer, NULL::integer, 0)::smallint > 0 OR b5.articolo
IS NULL) AND (COALESCE(b6.stato::integer, NULL::integer, 0)::smallint > 0 OR
b6.articolo IS NULL) AND (COALESCE(b7.stato::integer, NULL::integer,
0)::smallint > 0 OR b7.articolo IS NULL) AND (COALESCE(b8.stato::integer,
NULL::integer, 0)::smallint > 0 OR b8.articolo IS NULL) AND
(COALESCE(b9.stato::integer, NULL::integer, 0)::smallint > 0 OR b9.articolo
IS NULL) AND (COALESCE(b10.stato::integer, NULL::integer, 0)::smallint > 0
OR b10.articolo IS NULL) AND (COALESCE(b11.stato::integer, NULL::integer,
0)::smallint > 0 OR b11.articolo IS NULL) AND (COALESCE(b12.stato::integer,
NULL::integer, 0)::smallint > 0 OR b12.articolo IS NULL) AND
(COALESCE(b13.stato::integer, NULL::integer, 0)::smallint > 0 OR
b13.articolo IS NULL) AND (COALESCE(b14.stato::integer, NULL::integer,
0)::smallint > 0 OR b14.articolo IS NULL) AND (COALESCE(b15.stato::integer,
NULL::integer, 0)::smallint > 0 OR b15.articolo IS NULL) AND
(COALESCE(b16.stato::integer, NULL::integer, 0)::smallint > 0 OR
b16.articolo IS NULL) AND (COALESCE(b17.stato::integer, NULL::integer,
0)::smallint > 0 OR b17.articolo IS NULL) AND (COALESCE(b18.stato::integer,
NULL::integer, 0)::smallint > 0 OR b18.articolo IS NULL) AND
(COALESCE(b19.stato::integer, NULL::integer, 0)::smallint > 0 OR
b19.articolo IS NULL) AS chk_cli, (COALESCE(b20.stato, NULL::integer,
0)::smallint > 0 OR b20.articolo IS NULL) AND (COALESCE(b21.stato,
NULL::integer, 0)::smallint > 0 OR b21.articolo IS NULL) AND
(COALESCE(b22.stato, NULL::integer, 0)::smallint > 0 OR b22.articolo IS
NULL) AND (COALESCE(b23.stato, NULL::integer, 0)::smallint > 0 OR
b23.articolo IS NULL) AND (COALESCE(b24.stato, NULL::integer, 0)::smallint >
0 OR b24.articolo IS NULL) AND (COALESCE(b25.stato, NULL::integer,
0)::smallint > 0 OR b25.articolo IS NULL) AND (COALESCE(b26.stato,
NULL::integer, 0)::smallint > 0 OR b26.articolo IS NULL) AND
(COALESCE(b27.stato, NULL::integer, 0)::smallint > 0 OR b27.articolo IS
NULL) AND (COALESCE(b28.stato, NULL::integer, 0)::smallint > 0 OR
b28.articolo IS NULL) AND (COALESCE(b29.stato, NULL::integer, 0)::smallint >
0 OR b29.articolo IS NULL) AND (COALESCE(b30.stato, NULL::integer,
0)::smallint > 0 OR b30.articolo IS NULL) AND (COALESCE(b31.stato,
NULL::integer, 0)::smallint > 0 OR b31.articolo IS NULL) AND
(COALESCE(b32.stato, NULL::integer, 0)::smallint > 0 OR b32.articolo IS
NULL) AND (COALESCE(b33.stato, NULL::integer, 0)::smallint > 0 OR
b33.articolo IS NULL) AND (COALESCE(b34.stato, NULL::integer, 0)::smallint >
0 OR b34.articolo IS NULL) AND (COALESCE(b35.stato, NULL::integer,
0)::smallint > 0 OR b35.articolo IS NULL) AND (COALESCE(b36.stato,
NULL::integer, 0)::smallint > 0 OR b36.articolo IS NULL) AND
(COALESCE(b37.stato, NULL::integer, 0)::smallint > 0 OR b37.articolo IS
NULL) AND (COALESCE(b38.stato, NULL::integer, 0)::smallint > 0 OR
b38.articolo IS NULL) AND (COALESCE(b39.stato, NULL::integer, 0)::smallint >
0 OR b39.articolo IS NULL) AND (COALESCE(b40.stato, NULL::integer,
0)::smallint > 0 OR b40.articolo IS NULL) AND (COALESCE(b41.stato,
NULL::integer, 0)::smallint > 0 OR b41.articolo IS NULL) AND
(COALESCE(b42.stato, NULL::integer, 0)::smallint > 0 OR b42.articolo IS
NULL) AND (COALESCE(b43.stato, NULL::integer, 0)::smallint > 0 OR
b43.articolo IS NULL) AND (COALESCE(b44.stato, NULL::integer, 0)::smallint >
0 OR b44.articolo IS NULL) AND (COALESCE(b45.stato, NULL::integer,
0)::smallint > 0 OR b45.articolo IS NULL) AND (COALESCE(b46.stato,
NULL::integer, 0)::smallint > 0 OR b46.articolo IS NULL) AND
(COALESCE(b47.stato, NULL::integer, 0)::smallint > 0 OR b47.articolo IS
NULL) AND (COALESCE(b48.stato, NULL::integer, 0)::smallint > 0 OR
b48.articolo IS NULL) AND (COALESCE(b49.stato, NULL::integer, 0)::smallint >
0 OR b49.articolo IS NULL) AND (COALESCE(b50.stato, NULL::integer,
0)::smallint > 0 OR b50.articolo IS NULL) AND (COALESCE(b51.stato,
NULL::integer, 0)::smallint > 0 OR b51.articolo IS NULL) AND
(COALESCE(b52.stato, NULL::integer, 0)::smallint > 0 OR b52.articolo IS
NULL) AND (COALESCE(b53.stato, NULL::integer, 0)::smallint > 0 OR
b53.articolo IS NULL) AND (COALESCE(b54.stato, NULL::integer, 0)::smallint >
0 OR b54.articolo IS NULL) AND (COALESCE(b55.stato, NULL::integer,
0)::smallint > 0 OR b55.articolo IS NULL) AND (COALESCE(b56.stato,
NULL::integer, 0)::smallint > 0 OR b56.articolo IS NULL) AS chk_for,
b1.colli AS t_colli7717cli, b1.quantita AS t_qta7717cli, b1.stato AS
st7717cli, b1.eti_ok AS ok7717cli, b2.colli AS t_colli7705cli, b2.quantita
AS t_qta7705cli, b2.stato AS st7705cli, b2.eti_ok AS ok7705cli, b3.colli AS
t_colli7715cli, b3.quantita AS t_qta7715cli, b3.stato AS st7715cli,
b3.eti_ok AS ok7715cli, b4.colli AS t_colli7704cli, b4.quantita AS
t_qta7704cli, b4.stato AS st7704cli, b4.eti_ok AS ok7704cli, b5.colli AS
t_colli7714cli, b5.quantita AS t_qta7714cli, b5.stato AS st7714cli,
b5.eti_ok AS ok7714cli, b6.colli AS t_colli7718cli, b6.quantita AS
t_qta7718cli, b6.stato AS st7718cli, b6.eti_ok AS ok7718cli, b7.colli AS
t_colli7713cli, b7.quantita AS t_qta7713cli, b7.stato AS st7713cli,
b7.eti_ok AS ok7713cli, b8.colli AS t_colli7708cli, b8.quantita AS
t_qta7708cli, b8.stato AS st7708cli, b8.eti_ok AS ok7708cli, b9.colli AS
t_colli7719cli, b9.quantita AS t_qta7719cli, b9.stato AS st7719cli,
b9.eti_ok AS ok7719cli, b10.colli AS t_colli7709cli, b10.quantita AS
t_qta7709cli, b10.stato AS st7709cli, b10.eti_ok AS ok7709cli, b11.colli AS
t_colli7722cli, b11.quantita AS t_qta7722cli, b11.stato AS st7722cli,
b11.eti_ok AS ok7722cli, b12.colli AS t_colli7706cli, b12.quantita AS
t_qta7706cli, b12.stato AS st7706cli, b12.eti_ok AS ok7706cli, b13.colli AS
t_colli7716cli, b13.quantita AS t_qta7716cli, b13.stato AS st7716cli,
b13.eti_ok AS ok7716cli, b14.colli AS t_colli7721cli, b14.quantita AS
t_qta7721cli, b14.stato AS st7721cli, b14.eti_ok AS ok7721cli, b15.colli AS
t_colli7707cli, b15.quantita AS t_qta7707cli, b15.stato AS st7707cli,
b15.eti_ok AS ok7707cli, b16.colli AS t_colli7712cli, b16.quantita AS
t_qta7712cli, b16.stato AS st7712cli, b16.eti_ok AS ok7712cli, b17.colli AS
t_colli7711cli, b17.quantita AS t_qta7711cli, b17.stato AS st7711cli,
b17.eti_ok AS ok7711cli, b18.colli AS t_colli7710cli, b18.quantita AS
t_qta7710cli, b18.stato AS st7710cli, b18.eti_ok AS ok7710cli, b19.colli AS
t_colli7720cli, b19.quantita AS t_qta7720cli, b19.stato AS st7720cli,
b19.eti_ok AS ok7720cli, b20.colli AS t_colli18499for, b20.quantita AS
t_qta18499for, b20.stato AS st18499for, b21.colli AS t_colli18502for,
b21.quantita AS t_qta18502for, b21.stato AS st18502for, b22.colli AS
t_colli18469for, b22.quantita AS t_qta18469for, b22.stato AS st18469for,
b23.colli AS t_colli18475for, b23.quantita AS t_qta18475for, b23.stato AS
st18475for, b24.colli AS t_colli18472for, b24.quantita AS t_qta18472for,
b24.stato AS st18472for, b25.colli AS t_colli18462for, b25.quantita AS
t_qta18462for, b25.stato AS st18462for, b26.colli AS t_colli18479for,
b26.quantita AS t_qta18479for, b26.stato AS st18479for, b27.colli AS
t_colli18505for, b27.quantita AS t_qta18505for, b27.stato AS st18505for,
b28.colli AS t_colli18470for, b28.quantita AS t_qta18470for, b28.stato AS
st18470for, b29.colli AS t_colli18477for, b29.quantita AS t_qta18477for,
b29.stato AS st18477for, b30.colli AS t_colli18465for, b30.quantita AS
t_qta18465for, b30.stato AS st18465for, b31.colli AS t_colli18453for,
b31.quantita AS t_qta18453for, b31.stato AS st18453for, b32.colli AS
t_colli18457for, b32.quantita AS t_qta18457for, b32.stato AS st18457for,
b33.colli AS t_colli18450for, b33.quantita AS t_qta18450for, b33.stato AS
st18450for, b34.colli AS t_colli18451for, b34.quantita AS t_qta18451for,
b34.stato AS st18451for, b35.colli AS t_colli18454for, b35.quantita AS
t_qta18454for, b35.stato AS st18454for, b36.colli AS t_colli18456for,
b36.quantita AS t_qta18456for, b36.stato AS st18456for, b37.colli AS
t_colli18455for, b37.quantita AS t_qta18455for, b37.stato AS st18455for,
b38.colli AS t_colli18458for, b38.quantita AS t_qta18458for, b38.stato AS
st18458for, b39.colli AS t_colli18501for, b39.quantita AS t_qta18501for,
b39.stato AS st18501for, b40.colli AS t_colli18504for, b40.quantita AS
t_qta18504for, b40.stato AS st18504for, b41.colli AS t_colli18471for,
b41.quantita AS t_qta18471for, b41.stato AS st18471for, b42.colli AS
t_colli18500for, b42.quantita AS t_qta18500for, b42.stato AS st18500for,
b43.colli AS t_colli18503for, b43.quantita AS t_qta18503for, b43.stato AS
st18503for, b44.colli AS t_colli18461for, b44.quantita AS t_qta18461for,
b44.stato AS st18461for, b45.colli AS t_colli18478for, b45.quantita AS
t_qta18478for, b45.stato AS st18478for, b46.colli AS t_colli18467for,
b46.quantita AS t_qta18467for, b46.stato AS st18467for, b47.colli AS
t_colli18474for, b47.quantita AS t_qta18474for, b47.stato AS st18474for,
b48.colli AS t_colli18459for, b48.quantita AS t_qta18459for, b48.stato AS
st18459for, b49.colli AS t_colli18460for, b49.quantita AS t_qta18460for,
b49.stato AS st18460for, b50.colli AS t_colli18464for, b50.quantita AS
t_qta18464for, b50.stato AS st18464for, b51.colli AS t_colli18480for,
b51.quantita AS t_qta18480for, b51.stato AS st18480for, b52.colli AS
t_colli18506for, b52.quantita AS t_qta18506for, b52.stato AS st18506for,
b53.colli AS t_colli18452for, b53.quantita AS t_qta18452for, b53.stato AS
st18452for, b54.colli AS t_colli18463for, b54.quantita AS t_qta18463for,
b54.stato AS st18463for, b55.colli AS t_colli18466for, b55.quantita AS
t_qta18466for, b55.stato AS st18466for, b56.colli AS t_colli18473for,
b56.quantita AS t_qta18473for, b56.stato AS st18473for****FROM articoli a****
LEFT JOIN articoli_rim_rip_ven20110227_view rim ON rim.articolo =
a.articolo****LEFT JOIN articoli_rim_ass_rip_ven20110227_view rimass ON
rimass.articolo = a.articolo****LEFT JOIN bolrighe b1 ON b1.numero = 7717 AND b1.articolo = a.articolo*
***LEFT JOIN bolrighe b2 ON b2.numero = 7705 AND b2.articolo = a.articolo*
***LEFT JOIN bolrighe b3 ON b3.numero = 7715 AND b3.articolo = a.articolo*
***LEFT JOIN bolrighe b4 ON b4.numero = 7704 AND b4.articolo = a.articolo*
***LEFT JOIN bolrighe b5 ON b5.numero = 7714 AND b5.articolo = a.articolo*
***LEFT JOIN bolrighe b6 ON b6.numero = 7718 AND b6.articolo = a.articolo*
***LEFT JOIN bolrighe b7 ON b7.numero = 7713 AND b7.articolo = a.articolo*
***LEFT JOIN bolrighe b8 ON b8.numero = 7708 AND b8.articolo = a.articolo*
***LEFT JOIN bolrighe b9 ON b9.numero = 7719 AND b9.articolo = a.articolo*
***LEFT JOIN bolrighe b10 ON b10.numero = 7709 AND b10.articolo =
a.articolo****LEFT JOIN bolrighe b11 ON b11.numero = 7722 AND b11.articolo =
a.articolo****LEFT JOIN bolrighe b12 ON b12.numero = 7706 AND b12.articolo =
a.articolo****LEFT JOIN bolrighe b13 ON b13.numero = 7716 AND b13.articolo =
a.articolo****LEFT JOIN bolrighe b14 ON b14.numero = 7721 AND b14.articolo =
a.articolo****LEFT JOIN bolrighe b15 ON b15.numero = 7707 AND b15.articolo =
a.articolo****LEFT JOIN bolrighe b16 ON b16.numero = 7712 AND b16.articolo =
a.articolo****LEFT JOIN bolrighe b17 ON b17.numero = 7711 AND b17.articolo =
a.articolo****LEFT JOIN bolrighe b18 ON b18.numero = 7710 AND b18.articolo =
a.articolo****LEFT JOIN bolrighe b19 ON b19.numero = 7720 AND b19.articolo =
a.articolo****LEFT JOIN magmodet b20 ON b20.numero = 18499 AND b20.articolo =
a.articolo****LEFT JOIN magmodet b21 ON b21.numero = 18502 AND b21.articolo =
a.articolo****LEFT JOIN magmodet b22 ON b22.numero = 18469 AND b22.articolo =
a.articolo****LEFT JOIN magmodet b23 ON b23.numero = 18475 AND b23.articolo =
a.articolo****LEFT JOIN magmodet b24 ON b24.numero = 18472 AND b24.articolo =
a.articolo****LEFT JOIN magmodet b25 ON b25.numero = 18462 AND b25.articolo =
a.articolo****LEFT JOIN magmodet b26 ON b26.numero = 18479 AND b26.articolo =
a.articolo****LEFT JOIN magmodet b27 ON b27.numero = 18505 AND b27.articolo =
a.articolo****LEFT JOIN magmodet b28 ON b28.numero = 18470 AND b28.articolo =
a.articolo****LEFT JOIN magmodet b29 ON b29.numero = 18477 AND b29.articolo =
a.articolo****LEFT JOIN magmodet b30 ON b30.numero = 18465 AND b30.articolo =
a.articolo****LEFT JOIN magmodet b31 ON b31.numero = 18453 AND b31.articolo =
a.articolo****LEFT JOIN magmodet b32 ON b32.numero = 18457 AND b32.articolo =
a.articolo****LEFT JOIN magmodet b33 ON b33.numero = 18450 AND b33.articolo =
a.articolo****LEFT JOIN magmodet b34 ON b34.numero = 18451 AND b34.articolo =
a.articolo****LEFT JOIN magmodet b35 ON b35.numero = 18454 AND b35.articolo =
a.articolo****LEFT JOIN magmodet b36 ON b36.numero = 18456 AND b36.articolo =
a.articolo****LEFT JOIN magmodet b37 ON b37.numero = 18455 AND b37.articolo =
a.articolo****LEFT JOIN magmodet b38 ON b38.numero = 18458 AND b38.articolo =
a.articolo****LEFT JOIN magmodet b39 ON b39.numero = 18501 AND b39.articolo =
a.articolo****LEFT JOIN magmodet b40 ON b40.numero = 18504 AND b40.articolo =
a.articolo****LEFT JOIN magmodet b41 ON b41.numero = 18471 AND b41.articolo =
a.articolo****LEFT JOIN magmodet b42 ON b42.numero = 18500 AND b42.articolo =
a.articolo****LEFT JOIN magmodet b43 ON b43.numero = 18503 AND b43.articolo =
a.articolo****LEFT JOIN magmodet b44 ON b44.numero = 18461 AND b44.articolo =
a.articolo****LEFT JOIN magmodet b45 ON b45.numero = 18478 AND b45.articolo =
a.articolo****LEFT JOIN magmodet b46 ON b46.numero = 18467 AND b46.articolo =
a.articolo****LEFT JOIN magmodet b47 ON b47.numero = 18474 AND b47.articolo =
a.articolo****LEFT JOIN magmodet b48 ON b48.numero = 18459 AND b48.articolo =
a.articolo****LEFT JOIN magmodet b49 ON b49.numero = 18460 AND b49.articolo =
a.articolo****LEFT JOIN magmodet b50 ON b50.numero = 18464 AND b50.articolo =
a.articolo****LEFT JOIN magmodet b51 ON b51.numero = 18480 AND b51.articolo =
a.articolo****LEFT JOIN magmodet b52 ON b52.numero = 18506 AND b52.articolo =
a.articolo****LEFT JOIN magmodet b53 ON b53.numero = 18452 AND b53.articolo =
a.articolo****LEFT JOIN magmodet b54 ON b54.numero = 18463 AND b54.articolo =
a.articolo****LEFT JOIN magmodet b55 ON b55.numero = 18466 AND b55.articolo =
a.articolo****LEFT JOIN magmodet b56 ON b56.numero = 18473 AND b56.articolo =
a.articolo;****** **
Hello,
could you please post the query plan (in 9.1 and 9.0.5 and 8.3.3
) ? you have error ? the postgres freeze?
Kind Regards
Mat
Import Notes
Reply to msg id not found: 4ea52d61.09d8640a.5cbb.4acfSMTPIN_ADDED@mx.google.comReference msg id not found: 4ea52d61.09d8640a.5cbb.4acfSMTPIN_ADDED@mx.google.com | Resolved by subject fallback
...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.stehule@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 <o.bettin@informaticaindustriale.it>:
Show quoted text
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.attivitaFROM 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 +
COALESCE(b15.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b16.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b17.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b18.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b19.quantita, NULL::numeric, 0::numeric)::double precision) AS
diff_qta, COALESCE(b1.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b2.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b3.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b4.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b5.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b6.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b7.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b8.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b9.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b10.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b11.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b12.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b13.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b14.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b15.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b16.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b17.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b18.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b19.colli::integer, NULL::integer, 0)::smallint AS sum_colli_cli,
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 +
COALESCE(b15.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b16.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b17.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b18.quantita, NULL::numeric, 0::numeric)::double precision +
COALESCE(b19.quantita, NULL::numeric, 0::numeric)::double precision AS
sum_qta_cli, COALESCE(b20.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b21.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b22.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b23.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b24.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b25.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b26.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b27.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b28.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b29.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b30.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b31.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b32.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b33.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b34.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b35.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b36.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b37.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b38.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b39.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b40.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b41.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b42.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b43.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b44.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b45.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b46.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b47.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b48.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b49.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b50.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b51.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b52.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b53.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b54.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b55.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b56.colli::integer, NULL::integer, 0)::smallint +
(COALESCE(rim.colli, NULL::bigint, 0::bigint) + COALESCE(rimass.colli,
NULL::bigint, 0::bigint))::smallint AS sum_colli_for, 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 AS sum_qta_for, COALESCE(b20.colli::integer,
NULL::integer, 0)::smallint + COALESCE(b21.colli::integer, NULL::integer,
0)::smallint + COALESCE(b22.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b23.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b24.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b25.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b26.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b27.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b28.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b29.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b30.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b31.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b32.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b33.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b34.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b35.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b36.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b37.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b38.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b39.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b40.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b41.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b42.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b43.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b44.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b45.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b46.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b47.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b48.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b49.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b50.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b51.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b52.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b53.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b54.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b55.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b56.colli::integer, NULL::integer, 0)::smallint +
(COALESCE(rim.colli, NULL::bigint, 0::bigint) + COALESCE(rimass.colli,
NULL::bigint, 0::bigint))::smallint - (COALESCE(b1.colli::integer,
NULL::integer, 0)::smallint + COALESCE(b2.colli::integer, NULL::integer,
0)::smallint + COALESCE(b3.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b4.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b5.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b6.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b7.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b8.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b9.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b10.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b11.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b12.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b13.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b14.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b15.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b16.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b17.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b18.colli::integer, NULL::integer, 0)::smallint +
COALESCE(b19.colli::integer, NULL::integer, 0)::smallint) AS diff_colli,
(COALESCE(b1.stato::integer, NULL::integer, 0)::smallint > 0 OR b1.articolo
IS NULL) AND (COALESCE(b2.stato::integer, NULL::integer, 0)::smallint > 0 OR
b2.articolo IS NULL) AND (COALESCE(b3.stato::integer, NULL::integer,
0)::smallint > 0 OR b3.articolo IS NULL) AND (COALESCE(b4.stato::integer,
NULL::integer, 0)::smallint > 0 OR b4.articolo IS NULL) AND
(COALESCE(b5.stato::integer, NULL::integer, 0)::smallint > 0 OR b5.articolo
IS NULL) AND (COALESCE(b6.stato::integer, NULL::integer, 0)::smallint > 0 OR
b6.articolo IS NULL) AND (COALESCE(b7.stato::integer, NULL::integer,
0)::smallint > 0 OR b7.articolo IS NULL) AND (COALESCE(b8.stato::integer,
NULL::integer, 0)::smallint > 0 OR b8.articolo IS NULL) AND
(COALESCE(b9.stato::integer, NULL::integer, 0)::smallint > 0 OR b9.articolo
IS NULL) AND (COALESCE(b10.stato::integer, NULL::integer, 0)::smallint > 0
OR b10.articolo IS NULL) AND (COALESCE(b11.stato::integer, NULL::integer,
0)::smallint > 0 OR b11.articolo IS NULL) AND (COALESCE(b12.stato::integer,
NULL::integer, 0)::smallint > 0 OR b12.articolo IS NULL) AND
(COALESCE(b13.stato::integer, NULL::integer, 0)::smallint > 0 OR
b13.articolo IS NULL) AND (COALESCE(b14.stato::integer, NULL::integer,
0)::smallint > 0 OR b14.articolo IS NULL) AND (COALESCE(b15.stato::integer,
NULL::integer, 0)::smallint > 0 OR b15.articolo IS NULL) AND
(COALESCE(b16.stato::integer, NULL::integer, 0)::smallint > 0 OR
b16.articolo IS NULL) AND (COALESCE(b17.stato::integer, NULL::integer,
0)::smallint > 0 OR b17.articolo IS NULL) AND (COALESCE(b18.stato::integer,
NULL::integer, 0)::smallint > 0 OR b18.articolo IS NULL) AND
(COALESCE(b19.stato::integer, NULL::integer, 0)::smallint > 0 OR
b19.articolo IS NULL) AS chk_cli, (COALESCE(b20.stato, NULL::integer,
0)::smallint > 0 OR b20.articolo IS NULL) AND (COALESCE(b21.stato,
NULL::integer, 0)::smallint > 0 OR b21.articolo IS NULL) AND
(COALESCE(b22.stato, NULL::integer, 0)::smallint > 0 OR b22.articolo IS
NULL) AND (COALESCE(b23.stato, NULL::integer, 0)::smallint > 0 OR
b23.articolo IS NULL) AND (COALESCE(b24.stato, NULL::integer, 0)::smallint >
0 OR b24.articolo IS NULL) AND (COALESCE(b25.stato, NULL::integer,
0)::smallint > 0 OR b25.articolo IS NULL) AND (COALESCE(b26.stato,
NULL::integer, 0)::smallint > 0 OR b26.articolo IS NULL) AND
(COALESCE(b27.stato, NULL::integer, 0)::smallint > 0 OR b27.articolo IS
NULL) AND (COALESCE(b28.stato, NULL::integer, 0)::smallint > 0 OR
b28.articolo IS NULL) AND (COALESCE(b29.stato, NULL::integer, 0)::smallint >
0 OR b29.articolo IS NULL) AND (COALESCE(b30.stato, NULL::integer,
0)::smallint > 0 OR b30.articolo IS NULL) AND (COALESCE(b31.stato,
NULL::integer, 0)::smallint > 0 OR b31.articolo IS NULL) AND
(COALESCE(b32.stato, NULL::integer, 0)::smallint > 0 OR b32.articolo IS
NULL) AND (COALESCE(b33.stato, NULL::integer, 0)::smallint > 0 OR
b33.articolo IS NULL) AND (COALESCE(b34.stato, NULL::integer, 0)::smallint >
0 OR b34.articolo IS NULL) AND (COALESCE(b35.stato, NULL::integer,
0)::smallint > 0 OR b35.articolo IS NULL) AND (COALESCE(b36.stato,
NULL::integer, 0)::smallint > 0 OR b36.articolo IS NULL) AND
(COALESCE(b37.stato, NULL::integer, 0)::smallint > 0 OR b37.articolo IS
NULL) AND (COALESCE(b38.stato, NULL::integer, 0)::smallint > 0 OR
b38.articolo IS NULL) AND (COALESCE(b39.stato, NULL::integer, 0)::smallint >
0 OR b39.articolo IS NULL) AND (COALESCE(b40.stato, NULL::integer,
0)::smallint > 0 OR b40.articolo IS NULL) AND (COALESCE(b41.stato,
NULL::integer, 0)::smallint > 0 OR b41.articolo IS NULL) AND
(COALESCE(b42.stato, NULL::integer, 0)::smallint > 0 OR b42.articolo IS
NULL) AND (COALESCE(b43.stato, NULL::integer, 0)::smallint > 0 OR
b43.articolo IS NULL) AND (COALESCE(b44.stato, NULL::integer, 0)::smallint >
0 OR b44.articolo IS NULL) AND (COALESCE(b45.stato, NULL::integer,
0)::smallint > 0 OR b45.articolo IS NULL) AND (COALESCE(b46.stato,
NULL::integer, 0)::smallint > 0 OR b46.articolo IS NULL) AND
(COALESCE(b47.stato, NULL::integer, 0)::smallint > 0 OR b47.articolo IS
NULL) AND (COALESCE(b48.stato, NULL::integer, 0)::smallint > 0 OR
b48.articolo IS NULL) AND (COALESCE(b49.stato, NULL::integer, 0)::smallint >
0 OR b49.articolo IS NULL) AND (COALESCE(b50.stato, NULL::integer,
0)::smallint > 0 OR b50.articolo IS NULL) AND (COALESCE(b51.stato,
NULL::integer, 0)::smallint > 0 OR b51.articolo IS NULL) AND
(COALESCE(b52.stato, NULL::integer, 0)::smallint > 0 OR b52.articolo IS
NULL) AND (COALESCE(b53.stato, NULL::integer, 0)::smallint > 0 OR
b53.articolo IS NULL) AND (COALESCE(b54.stato, NULL::integer, 0)::smallint >
0 OR b54.articolo IS NULL) AND (COALESCE(b55.stato, NULL::integer,
0)::smallint > 0 OR b55.articolo IS NULL) AND (COALESCE(b56.stato,
NULL::integer, 0)::smallint > 0 OR b56.articolo IS NULL) AS chk_for,
b1.colli AS t_colli7717cli, b1.quantita AS t_qta7717cli, b1.stato AS
st7717cli, b1.eti_ok AS ok7717cli, b2.colli AS t_colli7705cli, b2.quantita
AS t_qta7705cli, b2.stato AS st7705cli, b2.eti_ok AS ok7705cli, b3.colli AS
t_colli7715cli, b3.quantita AS t_qta7715cli, b3.stato AS st7715cli,
b3.eti_ok AS ok7715cli, b4.colli AS t_colli7704cli, b4.quantita AS
t_qta7704cli, b4.stato AS st7704cli, b4.eti_ok AS ok7704cli, b5.colli AS
t_colli7714cli, b5.quantita AS t_qta7714cli, b5.stato AS st7714cli,
b5.eti_ok AS ok7714cli, b6.colli AS t_colli7718cli, b6.quantita AS
t_qta7718cli, b6.stato AS st7718cli, b6.eti_ok AS ok7718cli, b7.colli AS
t_colli7713cli, b7.quantita AS t_qta7713cli, b7.stato AS st7713cli,
b7.eti_ok AS ok7713cli, b8.colli AS t_colli7708cli, b8.quantita AS
t_qta7708cli, b8.stato AS st7708cli, b8.eti_ok AS ok7708cli, b9.colli AS
t_colli7719cli, b9.quantita AS t_qta7719cli, b9.stato AS st7719cli,
b9.eti_ok AS ok7719cli, b10.colli AS t_colli7709cli, b10.quantita AS
t_qta7709cli, b10.stato AS st7709cli, b10.eti_ok AS ok7709cli, b11.colli AS
t_colli7722cli, b11.quantita AS t_qta7722cli, b11.stato AS st7722cli,
b11.eti_ok AS ok7722cli, b12.colli AS t_colli7706cli, b12.quantita AS
t_qta7706cli, b12.stato AS st7706cli, b12.eti_ok AS ok7706cli, b13.colli AS
t_colli7716cli, b13.quantita AS t_qta7716cli, b13.stato AS st7716cli,
b13.eti_ok AS ok7716cli, b14.colli AS t_colli7721cli, b14.quantita AS
t_qta7721cli, b14.stato AS st7721cli, b14.eti_ok AS ok7721cli, b15.colli AS
t_colli7707cli, b15.quantita AS t_qta7707cli, b15.stato AS st7707cli,
b15.eti_ok AS ok7707cli, b16.colli AS t_colli7712cli, b16.quantita AS
t_qta7712cli, b16.stato AS st7712cli, b16.eti_ok AS ok7712cli, b17.colli AS
t_colli7711cli, b17.quantita AS t_qta7711cli, b17.stato AS st7711cli,
b17.eti_ok AS ok7711cli, b18.colli AS t_colli7710cli, b18.quantita AS
t_qta7710cli, b18.stato AS st7710cli, b18.eti_ok AS ok7710cli, b19.colli AS
t_colli7720cli, b19.quantita AS t_qta7720cli, b19.stato AS st7720cli,
b19.eti_ok AS ok7720cli, b20.colli AS t_colli18499for, b20.quantita AS
t_qta18499for, b20.stato AS st18499for, b21.colli AS t_colli18502for,
b21.quantita AS t_qta18502for, b21.stato AS st18502for, b22.colli AS
t_colli18469for, b22.quantita AS t_qta18469for, b22.stato AS st18469for,
b23.colli AS t_colli18475for, b23.quantita AS t_qta18475for, b23.stato AS
st18475for, b24.colli AS t_colli18472for, b24.quantita AS t_qta18472for,
b24.stato AS st18472for, b25.colli AS t_colli18462for, b25.quantita AS
t_qta18462for, b25.stato AS st18462for, b26.colli AS t_colli18479for,
b26.quantita AS t_qta18479for, b26.stato AS st18479for, b27.colli AS
t_colli18505for, b27.quantita AS t_qta18505for, b27.stato AS st18505for,
b28.colli AS t_colli18470for, b28.quantita AS t_qta18470for, b28.stato AS
st18470for, b29.colli AS t_colli18477for, b29.quantita AS t_qta18477for,
b29.stato AS st18477for, b30.colli AS t_colli18465for, b30.quantita AS
t_qta18465for, b30.stato AS st18465for, b31.colli AS t_colli18453for,
b31.quantita AS t_qta18453for, b31.stato AS st18453for, b32.colli AS
t_colli18457for, b32.quantita AS t_qta18457for, b32.stato AS st18457for,
b33.colli AS t_colli18450for, b33.quantita AS t_qta18450for, b33.stato AS
st18450for, b34.colli AS t_colli18451for, b34.quantita AS t_qta18451for,
b34.stato AS st18451for, b35.colli AS t_colli18454for, b35.quantita AS
t_qta18454for, b35.stato AS st18454for, b36.colli AS t_colli18456for,
b36.quantita AS t_qta18456for, b36.stato AS st18456for, b37.colli AS
t_colli18455for, b37.quantita AS t_qta18455for, b37.stato AS st18455for,
b38.colli AS t_colli18458for, b38.quantita AS t_qta18458for, b38.stato AS
st18458for, b39.colli AS t_colli18501for, b39.quantita AS t_qta18501for,
b39.stato AS st18501for, b40.colli AS t_colli18504for, b40.quantita AS
t_qta18504for, b40.stato AS st18504for, b41.colli AS t_colli18471for,
b41.quantita AS t_qta18471for, b41.stato AS st18471for, b42.colli AS
t_colli18500for, b42.quantita AS t_qta18500for, b42.stato AS st18500for,
b43.colli AS t_colli18503for, b43.quantita AS t_qta18503for, b43.stato AS
st18503for, b44.colli AS t_colli18461for, b44.quantita AS t_qta18461for,
b44.stato AS st18461for, b45.colli AS t_colli18478for, b45.quantita AS
t_qta18478for, b45.stato AS st18478for, b46.colli AS t_colli18467for,
b46.quantita AS t_qta18467for, b46.stato AS st18467for, b47.colli AS
t_colli18474for, b47.quantita AS t_qta18474for, b47.stato AS st18474for,
b48.colli AS t_colli18459for, b48.quantita AS t_qta18459for, b48.stato AS
st18459for, b49.colli AS t_colli18460for, b49.quantita AS t_qta18460for,
b49.stato AS st18460for, b50.colli AS t_colli18464for, b50.quantita AS
t_qta18464for, b50.stato AS st18464for, b51.colli AS t_colli18480for,
b51.quantita AS t_qta18480for, b51.stato AS st18480for, b52.colli AS
t_colli18506for, b52.quantita AS t_qta18506for, b52.stato AS st18506for,
b53.colli AS t_colli18452for, b53.quantita AS t_qta18452for, b53.stato AS
st18452for, b54.colli AS t_colli18463for, b54.quantita AS t_qta18463for,
b54.stato AS st18463for, b55.colli AS t_colli18466for, b55.quantita AS
t_qta18466for, b55.stato AS st18466for, b56.colli AS t_colli18473for,
b56.quantita AS t_qta18473for, b56.stato AS st18473forFROM articoli a
LEFT JOIN articoli_rim_rip_ven20110227_view rim ON rim.articolo =
a.articoloLEFT JOIN articoli_rim_ass_rip_ven20110227_view rimass ON rimass.articolo
= a.articoloLEFT JOIN bolrighe b1 ON b1.numero = 7717 AND b1.articolo = a.articolo
LEFT JOIN bolrighe b2 ON b2.numero = 7705 AND b2.articolo = a.articolo
LEFT JOIN bolrighe b3 ON b3.numero = 7715 AND b3.articolo = a.articolo
LEFT JOIN bolrighe b4 ON b4.numero = 7704 AND b4.articolo = a.articolo
LEFT JOIN bolrighe b5 ON b5.numero = 7714 AND b5.articolo = a.articolo
LEFT JOIN bolrighe b6 ON b6.numero = 7718 AND b6.articolo = a.articolo
LEFT JOIN bolrighe b7 ON b7.numero = 7713 AND b7.articolo = a.articolo
LEFT JOIN bolrighe b8 ON b8.numero = 7708 AND b8.articolo = a.articolo
LEFT JOIN bolrighe b9 ON b9.numero = 7719 AND b9.articolo = a.articolo
LEFT JOIN bolrighe b10 ON b10.numero = 7709 AND b10.articolo = a.articolo
LEFT JOIN bolrighe b11 ON b11.numero = 7722 AND b11.articolo = a.articolo
LEFT JOIN bolrighe b12 ON b12.numero = 7706 AND b12.articolo = a.articolo
LEFT JOIN bolrighe b13 ON b13.numero = 7716 AND b13.articolo = a.articolo
LEFT JOIN bolrighe b14 ON b14.numero = 7721 AND b14.articolo = a.articolo
LEFT JOIN bolrighe b15 ON b15.numero = 7707 AND b15.articolo = a.articolo
LEFT JOIN bolrighe b16 ON b16.numero = 7712 AND b16.articolo = a.articolo
LEFT JOIN bolrighe b17 ON b17.numero = 7711 AND b17.articolo = a.articolo
LEFT JOIN bolrighe b18 ON b18.numero = 7710 AND b18.articolo = a.articolo
LEFT JOIN bolrighe b19 ON b19.numero = 7720 AND b19.articolo = a.articolo
LEFT JOIN magmodet b20 ON b20.numero = 18499 AND b20.articolo =
a.articoloLEFT JOIN magmodet b21 ON b21.numero = 18502 AND b21.articolo =
a.articoloLEFT JOIN magmodet b22 ON b22.numero = 18469 AND b22.articolo =
a.articoloLEFT JOIN magmodet b23 ON b23.numero = 18475 AND b23.articolo =
a.articoloLEFT JOIN magmodet b24 ON b24.numero = 18472 AND b24.articolo =
a.articoloLEFT JOIN magmodet b25 ON b25.numero = 18462 AND b25.articolo =
a.articoloLEFT JOIN magmodet b26 ON b26.numero = 18479 AND b26.articolo =
a.articoloLEFT JOIN magmodet b27 ON b27.numero = 18505 AND b27.articolo =
a.articoloLEFT JOIN magmodet b28 ON b28.numero = 18470 AND b28.articolo =
a.articoloLEFT JOIN magmodet b29 ON b29.numero = 18477 AND b29.articolo =
a.articoloLEFT JOIN magmodet b30 ON b30.numero = 18465 AND b30.articolo =
a.articoloLEFT JOIN magmodet b31 ON b31.numero = 18453 AND b31.articolo =
a.articoloLEFT JOIN magmodet b32 ON b32.numero = 18457 AND b32.articolo =
a.articoloLEFT JOIN magmodet b33 ON b33.numero = 18450 AND b33.articolo =
a.articoloLEFT JOIN magmodet b34 ON b34.numero = 18451 AND b34.articolo =
a.articoloLEFT JOIN magmodet b35 ON b35.numero = 18454 AND b35.articolo =
a.articoloLEFT JOIN magmodet b36 ON b36.numero = 18456 AND b36.articolo =
a.articoloLEFT JOIN magmodet b37 ON b37.numero = 18455 AND b37.articolo =
a.articoloLEFT JOIN magmodet b38 ON b38.numero = 18458 AND b38.articolo =
a.articoloLEFT JOIN magmodet b39 ON b39.numero = 18501 AND b39.articolo =
a.articoloLEFT JOIN magmodet b40 ON b40.numero = 18504 AND b40.articolo =
a.articoloLEFT JOIN magmodet b41 ON b41.numero = 18471 AND b41.articolo =
a.articoloLEFT JOIN magmodet b42 ON b42.numero = 18500 AND b42.articolo =
a.articoloLEFT JOIN magmodet b43 ON b43.numero = 18503 AND b43.articolo =
a.articoloLEFT JOIN magmodet b44 ON b44.numero = 18461 AND b44.articolo =
a.articoloLEFT JOIN magmodet b45 ON b45.numero = 18478 AND b45.articolo =
a.articoloLEFT JOIN magmodet b46 ON b46.numero = 18467 AND b46.articolo =
a.articoloLEFT JOIN magmodet b47 ON b47.numero = 18474 AND b47.articolo =
a.articoloLEFT JOIN magmodet b48 ON b48.numero = 18459 AND b48.articolo =
a.articoloLEFT JOIN magmodet b49 ON b49.numero = 18460 AND b49.articolo =
a.articoloLEFT JOIN magmodet b50 ON b50.numero = 18464 AND b50.articolo =
a.articoloLEFT JOIN magmodet b51 ON b51.numero = 18480 AND b51.articolo =
a.articoloLEFT JOIN magmodet b52 ON b52.numero = 18506 AND b52.articolo =
a.articoloLEFT JOIN magmodet b53 ON b53.numero = 18452 AND b53.articolo =
a.articoloLEFT JOIN magmodet b54 ON b54.numero = 18463 AND b54.articolo =
a.articoloLEFT JOIN magmodet b55 ON b55.numero = 18466 AND b55.articolo =
a.articoloLEFT JOIN magmodet b56 ON b56.numero = 18473 AND b56.articolo =
a.articolo;
On Mon, Oct 24, 2011 at 4:57 AM, Omar Bettin
<o.bettin@informaticaindustriale.it> 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
Import Notes
Reply to msg id not found: 4ea52d62.6716ec0a.2de2.ffffdf32SMTPIN_ADDED@mx.google.comReference msg id not found: 4ea52d62.6716ec0a.2de2.ffffdf32SMTPIN_ADDED@mx.google.com | Resolved by subject fallback
...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.
"Omar Bettin" <o.bettin@informaticaindustriale.it> 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
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:tgl@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" <o.bettin@informaticaindustriale.it> 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
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
2011/10/24 Omar Bettin <o.bettin@informaticaindustriale.it>:
...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
Show quoted text
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
Import Notes
Reply to msg id not found: 4ea598ef.1274960a.2ef2.ffffb6afSMTPIN_ADDED@mx.google.comReference msg id not found: 4ea52d62.6716ec0a.2de2.ffffdf32SMTPIN_ADDED@mx.google.com