>> from pgAdmin, it takes 1 second.
>> When I run this command from script it takes 11 minutes!
>
>> Any idea why running this command from script takes 11 minutes?
>
> Different plans maybe? Try EXPLAIN ANALYZE in both cases.
Thank you. I tried
explain analyze SELECT r.dokumnr FROM rid r LEFT JOIN dok d USING
(dokumnr)
WHERE d.dokumnr IS NULL
with small database.
From script this command returns
Hash Left Join (cost=12.11..60.42 rows=1 width=4) (actual
time=105.473..105.473 rows=0 loops=1)
Hash Cond: (r.dokumnr = d.dokumnr)
Filter: (d.dokumnr IS NULL)
-> Seq Scan on rid r (cost=0.00..38.87 rows=687 width=4) (actual
time=2.144..90.823 rows=687 loops=1)
-> Hash (cost=10.38..10.38 rows=138 width=4) (actual time=13.925..13.925
rows=138 loops=1)
-> Seq Scan on dok d (cost=0.00..10.38 rows=138 width=4) (actual
time=1.715..13.812 rows=138 loops=1)
Total runtime: 105.542 ms
running in standalone it returns
Hash Left Join (cost=13.44..61.76 rows=1 width=4) (actual time=2.172..2.172
rows=0 loops=1)
Hash Cond: (r.dokumnr = d.dokumnr)
Filter: (d.dokumnr IS NULL)
-> Seq Scan on rid r (cost=0.00..38.87 rows=687 width=4) (actual
time=0.076..0.802 rows=687 loops=1)
-> Hash (cost=11.53..11.53 rows=153 width=4) (actual time=0.400..0.400
rows=138 loops=1)
-> Seq Scan on dok d (cost=0.00..11.53 rows=153 width=4) (actual
time=0.013..0.242 rows=138 loops=1)
Total runtime: 2.338 ms
I have no idea why this command runs 50 times slower in script.
ODBC driver inserts RELEASE SAVEPOINT and SAVEPOINT commands before every
statement. There is great explanation about his in
http://archives.postgresql.org/pgsql-odbc/2006-05/msg00078.php
Unfortunately, no connection string option is documented.
I havent found a way to disable this automatic SAVEPOINT insertion from odbc
connection string.
I havent got reply to my message from January, 18 2007 in odbc forum (I
posted again today).
Reading ODBC driver source this I expected that Protocol=-0 in
connection string should work but this does not. Probably I missed something
in C source.
However I think that this cannot slow down SELECT command speed.
> Do you have work_mem set the same in both cases?
Yes. I have same database server and same database.
Andrus.