Re: how to speed up query

>> 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.