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
____________________________________________________________________