pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedslow cursor
6+ messages / 5 participants
[nested] [flat]
* slow cursor
@ 2006-04-17 14:34 Sriram Dandapani <sdandapani@counterpane.com>
2006-04-18 04:55 ` Re: slow cursor Luckys <plpgsql@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Sriram Dandapani @ 2006-04-17 14:34 UTC (permalink / raw)
To: pgsql-performance
Hi
I have a cursor that fetches 150K rows and updates or inserts a table
with 150K rows.
It takes several minutes for the process to complete (about 15 minutes).
The select by itself (without cursor) gets all rows in 15 seconds.
Is there a way to optimize the cursor to fetch all records and speed up
the process. I still need to do the record by record processing
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: slow cursor
2006-04-17 14:34 slow cursor Sriram Dandapani <sdandapani@counterpane.com>
@ 2006-04-18 04:55 ` Luckys <plpgsql@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Luckys @ 2006-04-18 04:55 UTC (permalink / raw)
To: Sriram Dandapani <sdandapani@counterpane.com>; +Cc: pgsql-performance
This is one thing that I always try to avoid, a single INSERT INTO...SELECT
...FROM or single UPDATE is always faster compared to looping the same
within a cursor, unless its inevitable.
regards,
Luckys.
On 4/17/06, Sriram Dandapani <sdandapani@counterpane.com> wrote:
>
> Hi
>
>
>
> I have a cursor that fetches 150K rows and updates or inserts a table with
> 150K rows.
>
>
>
> It takes several minutes for the process to complete (about 15 minutes).
> The select by itself (without cursor) gets all rows in 15 seconds.
>
>
>
> Is there a way to optimize the cursor to fetch all records and speed up
> the process. I still need to do the record by record processing
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Slow cursor
@ 2011-10-26 12:43 Cezariusz Marek <cezariusz.marek@comarch.pl>
2011-10-26 12:49 ` Re: Slow cursor Gregg Jaskiewicz <gryzman@gmail.com>
2011-10-26 13:10 ` Re: Slow cursor Andres Freund <andres@anarazel.de>
0 siblings, 2 replies; 6+ messages in thread
From: Cezariusz Marek @ 2011-10-26 12:43 UTC (permalink / raw)
To: pgsql-performance
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
____________________________________________________________________
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Slow cursor
2011-10-26 12:43 Slow cursor Cezariusz Marek <cezariusz.marek@comarch.pl>
@ 2011-10-26 12:49 ` Gregg Jaskiewicz <gryzman@gmail.com>
2011-10-26 12:56 ` Re: Slow cursor Cezariusz Marek <cezariusz.marek@comarch.pl>
1 sibling, 1 reply; 6+ messages in thread
From: Gregg Jaskiewicz @ 2011-10-26 12:49 UTC (permalink / raw)
To: Cezariusz Marek <cezariusz.marek@comarch.pl>; +Cc: pgsql-performance
Do you really need to query the catalogues ? That on its own is not a
good idea if you want something to run fast and frequently.
--
GJ
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Slow cursor
2011-10-26 12:43 Slow cursor Cezariusz Marek <cezariusz.marek@comarch.pl>
2011-10-26 12:49 ` Re: Slow cursor Gregg Jaskiewicz <gryzman@gmail.com>
@ 2011-10-26 12:56 ` Cezariusz Marek <cezariusz.marek@comarch.pl>
0 siblings, 0 replies; 6+ messages in thread
From: Cezariusz Marek @ 2011-10-26 12:56 UTC (permalink / raw)
To: pgsql-performance
Gregg Jaskiewicz wrote:
> Do you really need to query the catalogues ? That on its own is not a
> good idea if you want something to run fast and frequently.
I know, but I've used it just to show the problem with cursors. I have the same problem
with other slow queries, which execute within x seconds in psql or pgAdmin, but for loops
in functions it takes at least x seconds to fetch each row. So please just imagine, that
it's an other slow query.
--
____________________________________________________________________
Cezariusz Marek mob: +48 608 646 494
http://www.comarch.com/ tel: +48 33 815 0734
____________________________________________________________________
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Slow cursor
2011-10-26 12:43 Slow cursor Cezariusz Marek <cezariusz.marek@comarch.pl>
@ 2011-10-26 13:10 ` Andres Freund <andres@anarazel.de>
1 sibling, 0 replies; 6+ messages in thread
From: Andres Freund @ 2011-10-26 13:10 UTC (permalink / raw)
To: pgsql-performance; +Cc: Cezariusz Marek <cezariusz.marek@comarch.pl>
Hi,
On Wednesday 26 Oct 2011 14:43:08 Cezariusz Marek wrote:
> 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?
Does the problem persist if you play around with cursor_tuple_fraction?
Andres
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2011-10-26 13:10 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-17 14:34 slow cursor Sriram Dandapani <sdandapani@counterpane.com>
2006-04-18 04:55 ` Luckys <plpgsql@gmail.com>
2011-10-26 12:43 Slow cursor Cezariusz Marek <cezariusz.marek@comarch.pl>
2011-10-26 12:49 ` Re: Slow cursor Gregg Jaskiewicz <gryzman@gmail.com>
2011-10-26 12:56 ` Re: Slow cursor Cezariusz Marek <cezariusz.marek@comarch.pl>
2011-10-26 13:10 ` Re: Slow cursor Andres Freund <andres@anarazel.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox