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=# select version(); version ------------------------------------------------------------- PostgreSQL 8.4.5, compiled by Visual C++ build 1400, 32-bit (1 row) dbupdater=# 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 = 'bdt_skarpa' dbupdater(# AND tc.table_schema = 'prod1' dbupdater(# AND tc.constraint_schema = tc.table_schema dbupdater(# AND tc.constraint_type IN ('PRIMARY KEY','FOREIGN KEY') dbupdater(# AND kcu.constraint_name = tc.constraint_name dbupdater(# AND kcu.constraint_schema = tc.constraint_schema dbupdater(# AND ccu.constraint_name = tc.constraint_name dbupdater(# AND ccu.constraint_schema = tc.table_schema dbupdater(# AND ccu.table_schema = tc.table_schema) AS kc ON col.column_name = kc.column_name dbupdater-# WHERE col.table_name = 'bdt_skarpa' dbupdater-# AND col.table_schema = '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=# \i cursor_test.sql CREATE FUNCTION dbupdater=# 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 FROM information_schema.table_constraints tc, information_schema.key_column_usage kcu, information_schema.constraint_column_usage AS ccu where tc.table_name = 'bdt_skarpa' AND tc.table_schema = 'prod1' AND tc.constraint_schema = tc.table_schema AND tc.constraint_type IN ('PRIMARY KEY','FOREIGN KEY') AND kcu.constraint_name = tc.constraint_name AND kcu.constraint_schema = tc.constraint_schema AND ccu.constraint_name = tc.constraint_name AND ccu.constraint_schema = tc.table_schema AND ccu.table_schema = tc.table_schema) AS kc ON col.column_name = kc.column_name WHERE col.table_name = 'bdt_skarpa' AND col.table_schema = '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; -- ____________________________________________________________________ Cezariusz Marek mob: +48 608 646 494 http://www.comarch.com/ tel: +48 33 815 0734 ____________________________________________________________________ -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
