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;
As I mentioned when I proposed it, the temp table may not even be
necessary. The important part is t
On Jun 13, 3:13 pm, "Andrus" <[EMAIL PROTECTED]> wrote:
(...)
> As I understand, only way to optimize the statement
>
> delete from firma1.rid where dokumnr not in (select dokumnr from
> firma1.dok);
>
> assuming that firma1.dok.dokumnr does not contain null values is to change
> it to
>
> CREATE
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 do
and script terminates after 5.5 hours running yesterday night.
This might be a good reason not to run the script as a single long
transaction --- it's probably accumulating locks on a lot of different
tables. Which would be fine if it was the only thing going on, but
evidently it isn't.
Thank
"Andrus" <[EMAIL PROTECTED]> writes:
> 2007-06-13 03:49:10 ERROR: deadlock detected
> 2007-06-13 03:49:10 DETAIL: Process 3280 waits for AccessExclusiveLock on
> relation 233893 of database 233756; blocked by process 2508.
> Process 2508 waits for ShareUpdateExclusiveLock on relation 233988 of
>
I cannot make much sense of this information. I can see no reason why
your script should take 11 minutes, while executing it from pgAdmin
would take only a second. How do you run the script?
I'm running my script from VFP client applicaton.
Application sends every statement to server separately
"Andrus" <[EMAIL PROTECTED]> writes:
> 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.
Do you have work_mem set the same in both
> delete from firma1.rid where dokumnr not in (select dokumnr from
>firma1.dok)
For future reference, I beleive the problem is the NOT IN. It has this
"feature" where if any of the rows it searches has a NULL, it will
return FALSE for *all* rows. So the whole table has to be scanned to
check t
On Mon, Jun 11, 2007 at 03:01:08PM +0300, Andrus wrote:
> > delete from firma1.rid where dokumnr not in (select dokumnr from
> >firma1.dok)
>
> Yes, it is nonsensial. However, this command should run fast even if it is
> nonsensial.
For future reference, I beleive the problem is the NOT IN. It
We don't know. You don't tell us what version you're running, show
us any EXPLAIN ANALYSE output, tell us about the data. . .
explain analyze delete from firma1.rid where dokumnr not in (select dokumnr
from firma1.dok)
produces
"Seq Scan on rid (cost=7703.59..98570208.00 rows=101210 width=
Ah! 3.) should read:
CREATE TEMP TABLE mydel AS SELECT DISTINCT dokumnr FROM firma1.rid;
DELETE FROM firma1.dok WHERE dokumnr NOT IN (SELECT dukumnr FROM
mydel);
I need to delete from firma1.rid table
So I cannot use this suggestion since firma1.dok.dokumnr is already unique
(primary key).
Theis is also primary key constraint in dok table:
CONSTRAINT dok_pkey PRIMARY KEY (dokumnr),
On a sidenote: this primary implements a unique index anyway. The
additional index is useless. You can delete it to save time and
storage.
(Or maybe this is just another discrepancy between reality a
This whole operation looks contradictory in several ways.
firma1.rid references firma1.dok on (dokumnr)
Therefore, referential integrity commands that there be NO rows in
firma1.rid with a dokumnr not present in firma1.dok.
Therefore your DELETE cannot possibly be deleting anything. It is
nonsens
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 th
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 D
Hi Andrus!
On Jun 12, 6:38 pm, "Andrus" <[EMAIL PROTECTED]> wrote:
> 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).
I cannot make much sense of this information. I can see no reason why
your script s
On Jun 11, 2:23 pm, "Andrus" <[EMAIL PROTECTED]> wrote:
> 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 s
On Jun 11, 2:01 pm, "Andrus" <[EMAIL PROTECTED]> wrote:
(...)
> > This index makes no sense at all:
> > CREATE INDEX dok_dokumnr_idx ON firma1.dok USING btree (dokumnr);
>
> I listed table structure and constraints partially.
> Theis is also primary key constraint in dok table:
>
> CONSTRAINT do
On Jun 9, 12:15 am, Erwin Brandstetter <[EMAIL PROTECTED]> wrote:
> 3.) Write results of the subquery in a temp table, then DELETE:
>
> CREATE TEMP TABLE mydel AS SELECT DISTINCT dokumnr FROM firma1.rid;
> DELETE FROM firma1.dok USING mydel WHERE firma1.rid.doumnr =
> mydel.doumnr;
Ah! 3.) should
Hi Andrus!
On Jun 8, 10:29 am, "Andrus" <[EMAIL PROTECTED]> wrote:
> How to speed up the query
>
> delete from firma1.rid where dokumnr not in (select dokumnr from firma1.dok)
> CREATE TABLE firma1.dok
> (
> doktyyp character(1) NOT NULL,
> dokumnr integer NOT NULL DEFAULT nextval('dok_dokumn
On Fri, Jun 08, 2007 at 11:29:12AM +0300, Andrus wrote:
> How to speed up the query
We don't know. You don't tell us what version you're running, show
us any EXPLAIN ANALYSE output, tell us about the data. . .
A
--
Andrew Sullivan | [EMAIL PROTECTED]
Unfortunately reformatting the Internet i
How to speed up the query
delete from firma1.rid where dokumnr not in (select dokumnr from firma1.dok)
which runs approx 30 minutes
I have dokumnr indexes on both tables, both tables are analyzed.
CREATE TABLE firma1.dok
(
doktyyp character(1) NOT NULL,
dokumnr integer NOT NULL DEFAULT next
22 matches
Mail list logo
