From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>, Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>, Peter Childs <Blue(dot)Dragon(at)blueyonder(dot)co(dot)uk>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: ADD FOREIGN KEY (was Re: [GENERAL] 7.4Beta) |
Date: | 2003-09-29 22:55:04 |
Message-ID: | 20030929155053.G371@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
On Mon, 29 Sep 2003, Tom Lane wrote:
> Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> > As an update, so far I still am getting better results with NOT EXISTS
> > than the left join.
>
> Hm. Don't suppose you were using EXPLAIN ANALYZE so we could see what's
> happening? This is clearly a planner failure, although I'm unsure if we
> can expect the planner to get the right answer with no pg_statistic entries.
The left join one seems to give me values like the following:
At sort_mem=4096
Merge Right Join (cost=9966071.76..10349763.45 rows=49501250 width=4)
(actual time=4383435.48..4383435.48 rows=0 loops=1)
Merge Cond: (("outer".a = "inner".b) AND ("outer".b = "inner".c))
Filter: ("outer".a IS NULL)
-> Index Scan using pktest_a_key on pktest (cost=0.00..52.00
rows=1000 width=8) (actual time=17.82..1609.97 rows=10000 loops=1)
-> Sort (cost=9966071.76..10089824.88 rows=49501250 width=8) (actual
time=3876614.87..4157850.82 rows=50000000 loops=1)
Sort Key: fktest.b, fktest.c
-> Seq Scan on fktest (cost=0.00..745099.00 rows=49501250
width=8) (actual time=4.09..273798.65 rows=50000000 loops=1)
Filter: ((b IS NOT NULL) AND (c IS NOT NULL))
Total runtime: 4384366.79 msec
(9 rows)
At sort_mem=128000
Merge Right Join (cost=69.32..134.00 rows=991 width=4) (actual
time=2183787.83..2183787.83 rows=0 loops=1)
Merge Cond: (("outer".a = "inner".b) AND ("outer".b = "inner".c))
Filter: ("outer".a IS NULL)
-> Index Scan using pktest_a_key on pktest (cost=0.00..52.00
rows=1000 width=8) (actual time=13.11..390.40 rows=10000
loops=1)
-> Sort (cost=69.32..71.79 rows=991 width=8) (actual
time=1944240.67..2048954.65 rows=50000000 loops=1)
Sort Key: fktest.b, fktest.c
-> Seq Scan on fktest (cost=0.00..20.00 rows=991 width=8)
(actual time=2.61..225967.79 rows=50000000 loops=1)
Filter: ((b IS NOT NULL) AND (c IS NOT NULL))
Total runtime: 2184348.78 msec
I haven't finished a run with it doing an index scan on fktestyet,
still...
The not exists gives me:
Seq Scan on fktest (cost=0.00..242021289.48 rows=24750625 width=8)
(actual time=2032607.68..2032607.68 rows=0 loops=1)
Filter: ((b IS NOT NULL) AND (c IS NOT NULL) AND (NOT (subplan)))
SubPlan
-> Index Scan using pktest_a_key on pktest (cost=0.00..4.83 rows=1
width=0) (actual time=0.03..0.03 rows=1 loops=50000000)
Index Cond: ((a = $0) AND (b = $1))
Total runtime: 2032607.87 msec
(6 rows)
But this time was one of the higher times for this query. I'd seen times
down at about 1400000 msec yesterday.
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Pflug | 2003-09-29 22:56:32 | Re: ADD FOREIGN KEY (was Re: [GENERAL] 7.4Beta) |
Previous Message | Joseph Shraibman | 2003-09-29 22:54:39 | Is there any way to force analyze to run on a whole table? |
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Pflug | 2003-09-29 22:56:32 | Re: ADD FOREIGN KEY (was Re: [GENERAL] 7.4Beta) |
Previous Message | Neil Conway | 2003-09-29 22:41:42 | Re: deprecating the use of OIDs |