Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 7C4F9B5DC04 for ; Wed, 26 Oct 2011 09:43:17 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 04639-01 for ; Wed, 26 Oct 2011 12:43:10 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from odzwierny2.comarch.com (odzwierny2.comarch.com [217.74.69.10]) by mail.postgresql.org (Postfix) with ESMTP id 0C420B5DBF5 for ; Wed, 26 Oct 2011 09:43:09 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) authenticated as cezariusz.marek@comarch.com by odzwierny2.comarch.com with esmtpsa (TLSv1:RC4-MD5:128)(ComArch ESMTP) id 1RJ2p2-0005kT-Sl for pgsql-performance@postgresql.org; Wed, 26 Oct 2011 14:43:08 +0200 From: "Cezariusz Marek" To: Subject: Slow cursor Date: Wed, 26 Oct 2011 14:43:08 +0200 Organization: Comarch SA Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable X-Mailer: Microsoft Office Outlook 11 Thread-Index: AcyT3NAzLI0lI4EbQi+zI+JdY5kNEg== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6109 X-CA-Antivirus: Scanned by ComArch Antivirus on odzwierny2.comarch.com X-CA-Is-Spam: No X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201110/218 X-Sequence-Number: 45440 Is there any known problem with slow cursors in PostgreSQL 8.4.5? I have a following query, which is slow (on my database it takes 11 = seconds to execute), probably should be rewritten, but it doesn't matter here. The problem = is, that in cursor, each fetch takes much longer (even few minutes!), while only the first = one should be slow. Am I doing something wrong? Explain analyze: http://explain.depesz.com/s/TDw Microsoft Windows XP [Wersja 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. d:\Temp>psql dbupdater postgres psql (8.4.5) WARNING: Console code page (852) differs from Windows code page (1250) 8-bit characters might not work correctly. See psql reference page "Notes for Windows users" for details. Type "help" for help. dbupdater=3D# select version(); version ------------------------------------------------------------- PostgreSQL 8.4.5, compiled by Visual C++ build 1400, 32-bit (1 row) dbupdater=3D# SELECT col.column_name AS nazwa_kolumny, = kc.constraint_type, kc.fk_table_name, kc.fk_column_name dbupdater-# FROM information_schema.columns col dbupdater-# LEFT OUTER JOIN (SELECT kcu.column_name, tc.constraint_type, = ccu.table_name AS fk_table_name, ccu.column_name AS fk_column_name dbupdater(# FROM information_schema.table_constraints tc, dbupdater(# information_schema.key_column_usage kcu, dbupdater(# information_schema.constraint_column_usage AS ccu dbupdater(# where tc.table_name =3D 'bdt_skarpa' dbupdater(# AND tc.table_schema =3D 'prod1' dbupdater(# AND tc.constraint_schema =3D tc.table_schema dbupdater(# AND tc.constraint_type IN ('PRIMARY KEY','FOREIGN KEY') dbupdater(# AND kcu.constraint_name =3D tc.constraint_name dbupdater(# AND kcu.constraint_schema =3D tc.constraint_schema dbupdater(# AND ccu.constraint_name =3D tc.constraint_name dbupdater(# AND ccu.constraint_schema =3D tc.table_schema dbupdater(# AND ccu.table_schema =3D tc.table_schema) AS kc ON = col.column_name =3D kc.column_name dbupdater-# WHERE col.table_name =3D 'bdt_skarpa' dbupdater-# AND col.table_schema =3D 'prod1'; nazwa_kolumny | constraint_type | fk_table_name = | fk_column_name --------------------------------+-----------------+----------------------= --------+------- --------- id | PRIMARY KEY | bdt_skarpa = | id href | | = | id_bufora_insert | | = | id_bufora_update | | = | id_techniczny_obiektu | | = | iip_local_id | | = | iip_name_space | | = | iip_version_id | | = | informacja_dodatkowa | | = | kat_dokladnosci_geom_fk | FOREIGN KEY | = bdt_sl_kat_dokladnosci | id omg_kat_istnienia_fk | FOREIGN KEY | omg_sl_kat_istnienia = | id omg_koniec_zycia_obiektu | | = | omg_rodzaj_repr_geom_fk | FOREIGN KEY | = omg_sl_rodzaj_repr_geom | id omg_start_zycia_obiektu | | = | omg_start_zycia_wersji_obiektu | | = | omg_uwagi | | = | omg_uzytkownik | | = | omg_zrodlo_danych_atr_fk | FOREIGN KEY | omg_sl_zrodla_danych = | id omg_zrodlo_danych_geom_fk | FOREIGN KEY | omg_sl_zrodla_danych = | id omp_geometria | | = | omp_koniec_obiekt | | = | omp_koniec_wersja_obiekt | | = | omp_nazwa | | = | omp_referencja_fk | FOREIGN KEY | = omp_powiazanie_obiektow_join | id omp_rodzaj_geometrii_id | FOREIGN KEY | = omg_sl_rodzaj_geometrii | id omp_start_obiekt | | = | omp_start_wersja_obiekt | | = | (27 rows) dbupdater=3D# \i cursor_test.sql CREATE FUNCTION dbupdater=3D# select cursor_test(); NOTICE: begin 2011-10-26 14:23:40.56+02 NOTICE: in loop id 2011-10-26 14:23:49.828+02 NOTICE: in loop href 2011-10-26 14:26:36.466+02 NOTICE: in loop id_bufora_insert 2011-10-26 14:28:04.6+02 NOTICE: in loop id_bufora_update 2011-10-26 14:29:33.108+02 NOTICE: in loop id_techniczny_obiektu 2011-10-26 14:31:00.66+02 NOTICE: in loop iip_local_id 2011-10-26 14:32:27.741+02 NOTICE: in loop iip_name_space 2011-10-26 14:33:58.383+02 NOTICE: in loop iip_version_id 2011-10-26 14:35:43.324+02 ... create or replace function cursor_test() returns void as $$ declare cur cursor for SELECT col.column_name AS nazwa_kolumny, = kc.constraint_type, kc.fk_table_name, kc.fk_column_name FROM information_schema.columns col LEFT OUTER JOIN (SELECT kcu.column_name, tc.constraint_type, = ccu.table_name AS fk_table_name, ccu.column_name AS fk_column_name=20 FROM information_schema.table_constraints tc, information_schema.key_column_usage kcu, information_schema.constraint_column_usage AS ccu=20 where tc.table_name =3D 'bdt_skarpa' AND tc.table_schema =3D 'prod1' AND tc.constraint_schema =3D tc.table_schema AND tc.constraint_type IN ('PRIMARY KEY','FOREIGN KEY') AND kcu.constraint_name =3D tc.constraint_name AND kcu.constraint_schema =3D tc.constraint_schema AND ccu.constraint_name =3D tc.constraint_name AND ccu.constraint_schema =3D tc.table_schema AND ccu.table_schema =3D tc.table_schema) AS kc ON col.column_name =3D = kc.column_name WHERE col.table_name =3D 'bdt_skarpa' AND col.table_schema =3D 'prod1'; rec record; begin raise notice 'begin %', clock_timestamp(); for rec in cur loop raise notice 'in loop % %', rec.nazwa_kolumny, clock_timestamp(); end loop; raise notice 'end %', clock_timestamp(); end; $$ language plpgsql; --=20 ____________________________________________________________________ Cezariusz Marek=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 = mob: +48 608 646 494 http://www.comarch.com/ =A0 tel: +48 33 815 0734 ____________________________________________________________________