Andrew,
>> How to speed up the query
>
> We don't know.
Thank you.
Explain seems to show that PostgreSQL makes sequential scan of whole dok
table for every rid table row.
This is very slow since dok contains 55963 and rid 202421 rows.
I expected that there exists some trick like to force this DELETE command to
use bitmaps by re-writing it using joins.
> You don't tell us what version you're running
"PostgreSQL 8.2.3 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(mingw-special)"
> , show us any EXPLAIN ANALYSE output
I tried
explain analyze delete from firma1.rid where dokumnr not in (select dokumnr
from firma1.dok)
but after 40 minutes this command is not finished.
I leave this query running for a weekend.
> , tell us about the data. . .
I'm creating PostgreSQL database and loading data to it from my application.
I use this type of delete commands to remove bad rows before adding foreign
keys.
My loading application is running 14 hours and is not finished.Server and
application are in the same fast computer with 2 GB RAM and 10000 RPM WD
Raptor HDD running Windows Vista.
postgresql.conf file is not modified, it is from PostgreSQL Windows
installer.
After 14 hours CPU usage is 50% (postgres process uses it all), no disk
activity.
I seems that PostgreSQL cannot use 100% of CPU in dual core processors when
creating new database and loading data using single connection. It can use
only 50% CPU.
Maybe two connections which can ran two child processes can use 100% of cpu
but I have no generic idea how to split database loading and foreign key
creation into two connections.
Andrus.