Re: how to speed up query - Mailing list pgsql-general

>> I tried
>>
>> CREATE TEMP TABLE mydel AS
>>  SELECT r.dokumnr
>>  FROM rid r
>>  LEFT JOIN dok d USING (dokumnr)
>>  WHERE d.dokumnr IS NULL;
>> DELETE FROM rid USING mydel WHERE rid.dokumnr =mydel.dokumnr;
>> drop table mydel;
>>
>> and this runs 1 seconds intead for 2.2 hours.
>>
>> Thank you very much.
>> This works!
>>
>> It's sad that PostgreSQL cannot optimize this delete statement
>> automatically.
>
>
> 1 second does sound a lot better than 2 hours, doesn't it? :)

1 second if for repeated runs from pgAdmin.
I my script same CREATE TEMP TABLE command takes appox 11 minutes for same
data (see log below).

> As to why Postgres seems to fail, I cannot say any more, as your
> description is unclear. I am pretty sure there is some
> misunderstanding, though.

After your suggested change my database creation script runs 6 hours.

Result database biggest 15 tables are:

      1  bilkaib  152MB
      2  omrid  146MB
      3  klient  130MB
      4  rid 120MB
      5  omdok 59MB
      6  dok  48MB
      7  mailbox 28MB
      8  report  19MB
      9  bilkaib_db_idx 16MB
     10  bilkaib_cr_idx 16MB
     11  bilkaib_pkey 14MB
     12  bilkaib_kuupaev_idx  13MB
     13  bilkaib_dokumnr_idx 11MB
     14  summav  9MB
     15  desktop 7MB

I used query

SELECT relname as Table_Name, relpages * 8/1024 as size_in_mb
 FROM pg_class
 where  relpages * 8/1024>0
 ORDER BY relpages DESC

for this.

Biggest database (bilkaib) load time is 8 minutes, it contains 329000
records.
Total data loading time is approx 49 minutes.

Remaining 5 hours are used for index and key creation. This seems too much.

Here is log file for minutes  49 .. 135 ie. first 86 minutes after loading
data.

It shows statements which ran more than 1 minute.

First number (49,4500) is the number minutes from start of script (starting
from database creation).

The slowest statement is

CREATE TEMP TABLE mydel AS
 SELECT r.dokumnr
 FROM rid r
 LEFT JOIN dok d USING (dokumnr)
 WHERE d.dokumnr IS NULL

which starts at minute 104 and has duration 11 minutes.

There seems to be no direct bottleneck: there are many commands with
duration  1.. 11 minutes.
I will run my script in today night to get complete timing.

Any idea how to increase speed ?

Andrus.

49,4500 Duration 1,4167 minutes: UPDATE dok SET krdokumnr=NULL WHERE
krDokumnr is NOT null AND doktyyp NOT IN ('G','O')

52,3167 Duration 2,8667 minutes:
UPDATE dok SET krdokumnr=NULL WHERE doktyyp='G' AND krdokumnr IS NOT NULL
and
   krdokumnr NOT in (select dokumnr from dok WHERE doktyyp='G')

55,1500 Duration 2,8333 minutes:
UPDATE dok SET krdokumnr=NULL WHERE doktyyp='O' AND krdokumnr IS NOT NULL
and
   krdokumnr NOT in (select dokumnr from dok WHERE doktyyp='O')

56,5667 Duration 1,4167 minutes:
ALTER TABLE dok ADD CHECK (krdokumnr IS NULL OR doktyyp IN('G','O'))

57,9833 Duration 1,4167 minutes:
ALTER TABLE dok ADD CHECK (dokumnr>0)

60,8333 Duration 2,8333 minutes:
ALTER TABLE dok ADD FOREIGN KEY (krdokumnr) REFERENCES dok
  ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE

62,2667 Duration 1,4333 minutes:
ALTER TABLE dok ALTER doktyyp SET NOT NULL

63,6833 Duration 1,4167 minutes:
ALTER TABLE dok ALTER kuupaev SET NOT NULL

65,1333 Duration 1,4500 minutes:

CREATE INDEX dok_kuupaev_idx ON dok (kuupaev)

66,5667 Duration 1,4333 minutes:
CREATE INDEX dok_krdokumnr_idx ON dok (krdokumnr)

68 Duration 1,4333 minutes:
CREATE INDEX dok_tellimus_idx ON dok (tellimus)

69,4333 Duration 1,4333 minutes:
CREATE INDEX dok_tasudok_idx ON dok (tasudok)

70,8833 Duration 1,4333 minutes:
CREATE INDEX dok_klient_idx ON dok (klient)

72,3167 Duration 1,4333 minutes:
CREATE INDEX dok_tasumata_idx ON dok (tasumata)

73,7500 Duration 1,4333 minutes:

CREATE UNIQUE INDEX dok_tasudok_unique_idx ON dok (doktyyp,tasudok)
    WHERE doktyyp IN ( 'T', 'U')

83,5000 Duration 9,7500 minutes:
CREATE INDEX rid_dokumnr_idx ON rid (dokumnr)

93,2500 Duration 9,7500 minutes:
CREATE INDEX rid_toode_idx ON rid (toode)

104,3500 Duration 11,1000 minutes: CREATE TEMP TABLE mydel AS
 SELECT r.dokumnr
 FROM rid r
 LEFT JOIN dok d USING (dokumnr)
 WHERE d.dokumnr IS NULL

114,0167 Duration 9,6500 minutes:
DELETE FROM rid USING mydel WHERE rid.dokumnr =mydel.dokumnr

125,1500 Duration 11,1333 minutes:

 ALTER TABLE rid ADD FOREIGN KEY (dokumnr) REFERENCES dok
  ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE

135,0833 Duration 9,9333 minutes:
ALTER TABLE rid ALTER dokumnr SET NOT NULL