pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedfull outer performance problem
12+ messages / 5 participants
[nested] [flat]
* full outer performance problem
@ 2005-05-10 09:03 Kim Bisgaard <kib+pg@dmi.dk>
2005-05-10 14:28 ` Re: full outer performance problem John A Meinel <john@arbash-meinel.com>
2005-05-10 14:45 ` Re: full outer performance problem Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 12+ messages in thread
From: Kim Bisgaard @ 2005-05-10 09:03 UTC (permalink / raw)
To: pgsql-performance
Hi,
I'm having problems with the query optimizer and FULL OUTER JOIN on
PostgreSQL 7.4. I cannot get it to use my indexes with full outer joins.
I might be naive, but I think that it should be possible?
I have two BIG tables (virtually identical) with 3 NOT NULL columns
Station_id, TimeObs, Temp_XXXX, with indexes on (Station_id, TimeObs)
and valid ANALYSE (set statistics=100). I want to join the two tables
with a FULL OUTER JOIN.
When I specify the query as:
select temp_max_60min,temp_dry_at_2m
from station s natural join
temp_dry_at_2m a full outer join temp_max_60min b using (station_id, timeobs)
where s.wmo_id=6065
and timeobs='2004-1-1 0:0:0'
and '2004-1-1 0:0:0' between s.startdate and s.enddate;
I get the correct results, BUT LOUSY performance, and the following explain:
Nested Loop Left Join (cost=5.84..163484.08 rows=1349 width=12) (actual time=66146.815..119005.381 rows=1 loops=1)
Filter: (COALESCE("outer".timeobs, "inner".timeobs) = '2004-01-01 00:00:00'::timestamp without time zone)
-> Hash Join (cost=5.84..155420.24 rows=1349 width=16) (actual time=8644.449..110836.038 rows=109826 loops=1)
Hash Cond: ("outer".station_id = "inner".station_id)
-> Seq Scan on temp_dry_at_2m a (cost=0.00..120615.94 rows=6956994 width=16) (actual time=0.024..104548.515 rows=6956994 loops=1)
-> Hash (cost=5.84..5.84 rows=1 width=4) (actual time=0.114..0.114 rows=0 loops=1)
-> Index Scan using wmo_idx on station (cost=0.00..5.84 rows=1 width=4) (actual time=0.105..0.108 rows=1 loops=1)
Index Cond: ((wmo_id = 6065) AND ('2004-01-01 00:00:00'::timestamp without time zone >= startdate) AND ('2004-01-01 00:00:00'::timestamp without time zone <= enddate))
-> Index Scan using temp_max_60min_idx on temp_max_60min b (cost=0.00..5.96 rows=1 width=20) (actual time=0.071..0.071 rows=0 loops=109826)
Index Cond: (("outer".station_id = b.station_id) AND ("outer".timeobs = b.timeobs))
Total runtime: 119005.499 ms
(11 rows)
If I change the query to (and thus negates the full outer join):
select temp_max_60min,temp_dry_at_2m
from station s natural join
temp_dry_at_2m a full outer join temp_max_60min b using (station_id, timeobs)
where s.wmo_id=6065
and _a.timeobs='2004-1-1 0:0:0' and b._timeobs='2004-1-1 0:0:0'
and '2004-1-1 0:0:0' between s.startdate and s.enddate;
I get wrong results (In the case where one of the records is missing in
one of the tables), BUT GOOD performance, and this query plan:
Nested Loop (cost=0.00..17.83 rows=1 width=12) (actual time=79.221..79.236 rows=1 loops=1)
-> Nested Loop (cost=0.00..11.82 rows=1 width=24) (actual time=65.517..65.526 rows=1 loops=1)
-> Index Scan using wmo_idx on station (cost=0.00..5.83 rows=1 width=4) (actual time=0.022..0.026 rows=1 loops=1)
Index Cond: ((wmo_id = 6065) AND ('2004-01-01 00:00:00'::timestamp without time zone >= startdate) AND ('2004-01-01 00:00:00'::timestamp without time zone <= enddate))
-> Index Scan using temp_max_60min_idx on temp_max_60min b (cost=0.00..5.97 rows=1 width=20) (actual time=65.483..65.486 rows=1 loops=1)
Index Cond: (("outer".station_id = b.station_id) AND (b.timeobs = '2004-01-01 00:00:00'::timestamp without time zone))
-> Index Scan using temp_dry_at_2m_idx on temp_dry_at_2m a (cost=0.00..6.00 rows=1 width=16) (actual time=13.694..13.698 rows=1 loops=1)
Index Cond: (("outer".station_id = a.station_id) AND (a.timeobs = '2004-01-01 00:00:00'::timestamp without time zone))
Total runtime: 79.340 ms
(9 rows)
If further info like EXPLAIN VERBOSE is useful please say so and I will
provide it.
Thanks in advance!
Kim Bisgaard.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: full outer performance problem
2005-05-10 09:03 full outer performance problem Kim Bisgaard <kib+pg@dmi.dk>
@ 2005-05-10 14:28 ` John A Meinel <john@arbash-meinel.com>
2005-05-11 08:20 ` Re: full outer performance problem Kim Bisgaard <kib@dmi.dk>
1 sibling, 1 reply; 12+ messages in thread
From: John A Meinel @ 2005-05-10 14:28 UTC (permalink / raw)
To: Kim Bisgaard <kib+pg@dmi.dk>; +Cc: pgsql-performance
Kim Bisgaard wrote:
> Hi,
>
> I'm having problems with the query optimizer and FULL OUTER JOIN on
> PostgreSQL 7.4. I cannot get it to use my indexes with full outer joins.
> I might be naive, but I think that it should be possible?
>
> I have two BIG tables (virtually identical) with 3 NOT NULL columns
> Station_id, TimeObs, Temp_XXXX, with indexes on (Station_id, TimeObs)
> and valid ANALYSE (set statistics=100). I want to join the two tables
> with a FULL OUTER JOIN.
>
> When I specify the query as:
>
> select temp_max_60min,temp_dry_at_2m
> from station s natural join
> temp_dry_at_2m a full outer join temp_max_60min b using (station_id, timeobs)
> where s.wmo_id=6065
> and timeobs='2004-1-1 0:0:0'
> and '2004-1-1 0:0:0' between s.startdate and s.enddate;
>
> I get the correct results, BUT LOUSY performance, and the following explain:
>
> Nested Loop Left Join (cost=5.84..163484.08 rows=1349 width=12) (actual time=66146.815..119005.381 rows=1 loops=1)
> Filter: (COALESCE("outer".timeobs, "inner".timeobs) = '2004-01-01 00:00:00'::timestamp without time zone)
> -> Hash Join (cost=5.84..155420.24 rows=1349 width=16) (actual time=8644.449..110836.038 rows=109826 loops=1)
Well, the estimate here is quite a bit off. It thinks you will be
getting 1349 (which is probably why it picked a nested loop plan), but
then it is getting 109826 rows.
I'm guessing it is misunderstanding the selectivity of the timeobs column.
> Hash Cond: ("outer".station_id = "inner".station_id)
> -> Seq Scan on temp_dry_at_2m a (cost=0.00..120615.94 rows=6956994 width=16) (actual time=0.024..104548.515 rows=6956994 loops=1)
> -> Hash (cost=5.84..5.84 rows=1 width=4) (actual time=0.114..0.114 rows=0 loops=1)
> -> Index Scan using wmo_idx on station (cost=0.00..5.84 rows=1 width=4) (actual time=0.105..0.108 rows=1 loops=1)
> Index Cond: ((wmo_id = 6065) AND ('2004-01-01 00:00:00'::timestamp without time zone >= startdate) AND ('2004-01-01 00:00:00'::timestamp without time zone <= enddate))
> -> Index Scan using temp_max_60min_idx on temp_max_60min b (cost=0.00..5.96 rows=1 width=20) (actual time=0.071..0.071 rows=0 loops=109826)
> Index Cond: (("outer".station_id = b.station_id) AND ("outer".timeobs = b.timeobs))
> Total runtime: 119005.499 ms
> (11 rows)
I think the bigger problem is that a full outer join says grab all rows,
even if they are null.
What about this query:
SELECT temp_max_60min,temp_dry_at_2m
FROM (station s LEFT JOIN temp_dry_at_2m a USING (station_id, timeobs)
LEFT JOIN temp_max_60min b USING (station_id, timeobs)
where s.wmo_id=6065
and timeobs='2004-1-1 0:0:0'
and '2004-1-1 0:0:0' between s.startdate and s.enddate;
After that, you should probably have a multi-column index on
(station_id, timeobs), which lets postgres use just that index for the
lookup, rather than using an index and then a filter. (Looking at your
next query you might already have that index).
>
> If I change the query to (and thus negates the full outer join):
This is the same query, I think you messed up your copy and paste.
>
> select temp_max_60min,temp_dry_at_2m
> from station s natural join
> temp_dry_at_2m a full outer join temp_max_60min b using (station_id, timeobs)
> where s.wmo_id=6065
> and _a.timeobs='2004-1-1 0:0:0' and b._timeobs='2004-1-1 0:0:0'
> and '2004-1-1 0:0:0' between s.startdate and s.enddate;
>
>
> I get wrong results (In the case where one of the records is missing in
> one of the tables), BUT GOOD performance, and this query plan:
>
> Nested Loop (cost=0.00..17.83 rows=1 width=12) (actual time=79.221..79.236 rows=1 loops=1)
> -> Nested Loop (cost=0.00..11.82 rows=1 width=24) (actual time=65.517..65.526 rows=1 loops=1)
> -> Index Scan using wmo_idx on station (cost=0.00..5.83 rows=1 width=4) (actual time=0.022..0.026 rows=1 loops=1)
> Index Cond: ((wmo_id = 6065) AND ('2004-01-01 00:00:00'::timestamp without time zone >= startdate) AND ('2004-01-01 00:00:00'::timestamp without time zone <= enddate))
> -> Index Scan using temp_max_60min_idx on temp_max_60min b (cost=0.00..5.97 rows=1 width=20) (actual time=65.483..65.486 rows=1 loops=1)
> Index Cond: (("outer".station_id = b.station_id) AND (b.timeobs = '2004-01-01 00:00:00'::timestamp without time zone))
> -> Index Scan using temp_dry_at_2m_idx on temp_dry_at_2m a (cost=0.00..6.00 rows=1 width=16) (actual time=13.694..13.698 rows=1 loops=1)
> Index Cond: (("outer".station_id = a.station_id) AND (a.timeobs = '2004-01-01 00:00:00'::timestamp without time zone))
> Total runtime: 79.340 ms
> (9 rows)
>
>
> If further info like EXPLAIN VERBOSE is useful please say so and I will
> provide it.
>
> Thanks in advance!
> Kim Bisgaard.
I still feel like you will have a problem with an outer join in this
circumstance, because it will have to scan all of both tables.
I think what you are wanting is "give me everything where station_id =
X, and there is a row in either a or b".
I think my LEFT JOIN example does that, but I also think there would be
a subselect form which would work, which might do better. Something like:
SELECT temp_max_60min,temp_dry_at_2m
FROM (SELECT station_id, timeobs FROM station s
WHERE s.wmo_id=6065
AND timeobs = '2004-1-1 0:0:0'
AND '2004-1-1 0:0:0' BETWEEN s.startdate AND s.enddate
) AS s
JOIN (SELECT temp_max_60min, temp_dry_at_2m
FROM temp_dry_at_2m a
FULL OUTER JOIN temp_max_60min b
USING (station_id, timeobs)
WHERE station_id = s.station_id
AND timeobs = '2004-1-1 0:0:0'
)
;
If I did this correctly, you should have a very restrictive scan done on
station, which only returns a few rows based on timeobs & station_id.
But it might be better to turn that final FULL OUTER JOIN into 2 LEFT
JOINs like I did the first time:
SELECT temp_max_60min,temp_dry_at_2m
FROM (SELECT station_id, timeobs FROM station s
WHERE s.wmo_id=6065
AND timeobs = '2004-1-1 0:0:0'
AND '2004-1-1 0:0:0' BETWEEN s.startdate AND s.enddate
) AS s
LEFT JOIN temp_dry_at_2m a USING (station_id, timeobs)
LEFT JOIN temp_max_60min b USING (station_id, timeobs)
;
I would hope postgres could do this from just my earlier plan. And I
hope I understand what you want, such that 2 LEFT JOINS work better than
your FULL OUTER JOIN. If you only want rows where one of both temp_dry
or temp_max exist, you probably could just add the line:
WHERE (temp_max_60_min IS NOT NULL OR temp_dry_at_2m IS NOT NULL)
John
=:->
Attachments:
signature.asc (application/pgp-signature, 250B) download
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: full outer performance problem
2005-05-10 09:03 full outer performance problem Kim Bisgaard <kib+pg@dmi.dk>
2005-05-10 14:28 ` Re: full outer performance problem John A Meinel <john@arbash-meinel.com>
@ 2005-05-11 08:20 ` Kim Bisgaard <kib@dmi.dk>
0 siblings, 0 replies; 12+ messages in thread
From: Kim Bisgaard @ 2005-05-11 08:20 UTC (permalink / raw)
To: John A Meinel <john@arbash-meinel.com>; +Cc: pgsql-performance
Hi,
Look for my comments further down...
John A Meinel wrote:
> Kim Bisgaard wrote:
>
>> Hi,
>>
>> I'm having problems with the query optimizer and FULL OUTER JOIN on
>> PostgreSQL 7.4. I cannot get it to use my indexes with full outer joins.
>> I might be naive, but I think that it should be possible?
>>
>> I have two BIG tables (virtually identical) with 3 NOT NULL columns
>> Station_id, TimeObs, Temp_XXXX, with indexes on (Station_id, TimeObs)
>> and valid ANALYSE (set statistics=100). I want to join the two tables
>> with a FULL OUTER JOIN.
>>
>> When I specify the query as:
>>
>> select temp_max_60min,temp_dry_at_2m
>> from station s natural join
>> temp_dry_at_2m a full outer join temp_max_60min b using (station_id,
>> timeobs)
>> where s.wmo_id=6065
>> and timeobs='2004-1-1 0:0:0'
>> and '2004-1-1 0:0:0' between s.startdate and s.enddate;
>>
>> I get the correct results, BUT LOUSY performance, and the following
>> explain:
>>
>> Nested Loop Left Join (cost=5.84..163484.08 rows=1349 width=12)
>> (actual time=66146.815..119005.381 rows=1 loops=1)
>> Filter: (COALESCE("outer".timeobs, "inner".timeobs) = '2004-01-01
>> 00:00:00'::timestamp without time zone)
>> -> Hash Join (cost=5.84..155420.24 rows=1349 width=16) (actual
>> time=8644.449..110836.038 rows=109826 loops=1)
>
>
> Well, the estimate here is quite a bit off. It thinks you will be
> getting 1349 (which is probably why it picked a nested loop plan), but
> then it is getting 109826 rows.
> I'm guessing it is misunderstanding the selectivity of the timeobs
> column.
I think you are right..
>
>> Hash Cond: ("outer".station_id = "inner".station_id)
>> -> Seq Scan on temp_dry_at_2m a (cost=0.00..120615.94
>> rows=6956994 width=16) (actual time=0.024..104548.515 rows=6956994
>> loops=1)
>> -> Hash (cost=5.84..5.84 rows=1 width=4) (actual
>> time=0.114..0.114 rows=0 loops=1)
>> -> Index Scan using wmo_idx on station
>> (cost=0.00..5.84 rows=1 width=4) (actual time=0.105..0.108 rows=1
>> loops=1)
>> Index Cond: ((wmo_id = 6065) AND ('2004-01-01
>> 00:00:00'::timestamp without time zone >= startdate) AND ('2004-01-01
>> 00:00:00'::timestamp without time zone <= enddate))
>> -> Index Scan using temp_max_60min_idx on temp_max_60min b
>> (cost=0.00..5.96 rows=1 width=20) (actual time=0.071..0.071 rows=0
>> loops=109826)
>> Index Cond: (("outer".station_id = b.station_id) AND
>> ("outer".timeobs = b.timeobs))
>> Total runtime: 119005.499 ms
>> (11 rows)
>
>
> I think the bigger problem is that a full outer join says grab all rows,
> even if they are null.
>
> What about this query:
> SELECT temp_max_60min,temp_dry_at_2m
> FROM (station s LEFT JOIN temp_dry_at_2m a USING (station_id, timeobs)
> LEFT JOIN temp_max_60min b USING (station_id, timeobs)
> where s.wmo_id=6065
> and timeobs='2004-1-1 0:0:0'
> and '2004-1-1 0:0:0' between s.startdate and s.enddate;
This works very well, and gives the correct result - thanks!!
>
> After that, you should probably have a multi-column index on
> (station_id, timeobs), which lets postgres use just that index for the
> lookup, rather than using an index and then a filter. (Looking at your
> next query you might already have that index).
Yes I have.
>
>>
>> If I change the query to (and thus negates the full outer join):
>
>
> This is the same query, I think you messed up your copy and paste.
Nope. Changed "and timeobs='2004-1-1 0:0:0' " to "and
a.timeobs='2004-1-1 0:0:0' and b.timeobs='2004-1-1 0:0:0' "
>
>>
>> select temp_max_60min,temp_dry_at_2m
>> from station s natural join
>> temp_dry_at_2m a full outer join temp_max_60min b using (station_id,
>> timeobs)
>> where s.wmo_id=6065
>> and _a.timeobs='2004-1-1 0:0:0' and b._timeobs='2004-1-1 0:0:0'
>> and '2004-1-1 0:0:0' between s.startdate and s.enddate;
>>
>>
>> I get wrong results (In the case where one of the records is missing in
>> one of the tables), BUT GOOD performance, and this query plan:
>>
>> Nested Loop (cost=0.00..17.83 rows=1 width=12) (actual
>> time=79.221..79.236 rows=1 loops=1)
>> -> Nested Loop (cost=0.00..11.82 rows=1 width=24) (actual
>> time=65.517..65.526 rows=1 loops=1)
>> -> Index Scan using wmo_idx on station (cost=0.00..5.83
>> rows=1 width=4) (actual time=0.022..0.026 rows=1 loops=1)
>> Index Cond: ((wmo_id = 6065) AND ('2004-01-01
>> 00:00:00'::timestamp without time zone >= startdate) AND ('2004-01-01
>> 00:00:00'::timestamp without time zone <= enddate))
>> -> Index Scan using temp_max_60min_idx on temp_max_60min b
>> (cost=0.00..5.97 rows=1 width=20) (actual time=65.483..65.486 rows=1
>> loops=1)
>> Index Cond: (("outer".station_id = b.station_id) AND
>> (b.timeobs = '2004-01-01 00:00:00'::timestamp without time zone))
>> -> Index Scan using temp_dry_at_2m_idx on temp_dry_at_2m a
>> (cost=0.00..6.00 rows=1 width=16) (actual time=13.694..13.698 rows=1
>> loops=1)
>> Index Cond: (("outer".station_id = a.station_id) AND
>> (a.timeobs = '2004-01-01 00:00:00'::timestamp without time zone))
>> Total runtime: 79.340 ms
>> (9 rows)
>>
>>
>> If further info like EXPLAIN VERBOSE is useful please say so and I will
>> provide it.
>>
>> Thanks in advance!
>> Kim Bisgaard.
>
>
> I still feel like you will have a problem with an outer join in this
> circumstance, because it will have to scan all of both tables.
Maybe I misunderstand outer joins but since there are no rows with
NULLs, I think it is a matter of finding the rows that are there or
makeing one up one if there are no rows?
>
> I think what you are wanting is "give me everything where station_id =
> X, and there is a row in either a or b".
> I think my LEFT JOIN example does that, but I also think there would be
> a subselect form which would work, which might do better. Something like:
>
> SELECT temp_max_60min,temp_dry_at_2m
> FROM (SELECT station_id, timeobs FROM station s
> WHERE s.wmo_id=6065
> AND timeobs = '2004-1-1 0:0:0'
> AND '2004-1-1 0:0:0' BETWEEN s.startdate AND s.enddate
> ) AS s
> JOIN (SELECT temp_max_60min, temp_dry_at_2m
> FROM temp_dry_at_2m a
> FULL OUTER JOIN temp_max_60min b
> USING (station_id, timeobs)
> WHERE station_id = s.station_id
> AND timeobs = '2004-1-1 0:0:0'
> )
> ;
>
> If I did this correctly, you should have a very restrictive scan done on
> station, which only returns a few rows based on timeobs & station_id.
> But it might be better to turn that final FULL OUTER JOIN into 2 LEFT
> JOINs like I did the first time:
>
> SELECT temp_max_60min,temp_dry_at_2m
> FROM (SELECT station_id, timeobs FROM station s
> WHERE s.wmo_id=6065
> AND timeobs = '2004-1-1 0:0:0'
> AND '2004-1-1 0:0:0' BETWEEN s.startdate AND s.enddate
> ) AS s
> LEFT JOIN temp_dry_at_2m a USING (station_id, timeobs)
> LEFT JOIN temp_max_60min b USING (station_id, timeobs)
> ;
>
> I would hope postgres could do this from just my earlier plan. And I
> hope I understand what you want, such that 2 LEFT JOINS work better than
> your FULL OUTER JOIN. If you only want rows where one of both temp_dry
> or temp_max exist, you probably could just add the line:
>
> WHERE (temp_max_60_min IS NOT NULL OR temp_dry_at_2m IS NOT NULL)
Thanks John. You have opened my eyes for a new way to formulate my queries!
>
>
> John
> =:->
--
Kim Bisgaard
Computer Department Phone: +45 3915 7562 (direct)
Danish Meteorological Institute Fax: +45 3915 7460 (division)
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: full outer performance problem
2005-05-10 09:03 full outer performance problem Kim Bisgaard <kib+pg@dmi.dk>
@ 2005-05-10 14:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-05-11 07:05 ` Re: full outer performance problem Kim Bisgaard <kib@dmi.dk>
1 sibling, 1 reply; 12+ messages in thread
From: Tom Lane @ 2005-05-10 14:45 UTC (permalink / raw)
To: Kim Bisgaard <kib+pg@dmi.dk>; +Cc: pgsql-performance
Kim Bisgaard <kib+pg@dmi.dk> writes:
> I have two BIG tables (virtually identical) with 3 NOT NULL columns
> Station_id, TimeObs, Temp_XXXX, with indexes on (Station_id, TimeObs)
> and valid ANALYSE (set statistics=100). I want to join the two tables
> with a FULL OUTER JOIN.
I'm confused. If the columns are NOT NULL, why isn't this a valid
transformation of your original query?
> select temp_max_60min,temp_dry_at_2m
> from station s natural join
> temp_dry_at_2m a full outer join temp_max_60min b using (station_id, timeobs)
> where s.wmo_id=6065
> and _a.timeobs='2004-1-1 0:0:0' and b._timeobs='2004-1-1 0:0:0'
> and '2004-1-1 0:0:0' between s.startdate and s.enddate;
Seems like it's not eliminating any rows that would otherwise succeed.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: full outer performance problem
2005-05-10 09:03 full outer performance problem Kim Bisgaard <kib+pg@dmi.dk>
2005-05-10 14:45 ` Re: full outer performance problem Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-05-11 07:05 ` Kim Bisgaard <kib@dmi.dk>
0 siblings, 0 replies; 12+ messages in thread
From: Kim Bisgaard @ 2005-05-11 07:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Sorry for not listing the exact layout of temp_XXXX:
obsdb=> \d temp_dry_at_2m
Table "public.temp_dry_at_2m"
Column | Type | Modifiers
----------------+-----------------------------+-----------
obshist_id | integer | not null
station_id | integer | not null
timeobs | timestamp without time zone | not null
temp_dry_at_2m | real | not null
Indexes:
"temp_dry_at_2m_pkey" primary key, btree (obshist_id)
"temp_dry_at_2m_idx" btree (station_id, timeobs)
The difference between the two queries is if a (station_id,timeobs) row
is missing in one table, then the first returns one record(null,9.3)
while the second return no records.
Regards,
Kim Bisgaard.
Tom Lane wrote:
>Kim Bisgaard <kib+pg@dmi.dk> writes:
>
>
>>I have two BIG tables (virtually identical) with 3 NOT NULL columns
>>Station_id, TimeObs, Temp_XXXX, with indexes on (Station_id, TimeObs)
>>and valid ANALYSE (set statistics=100). I want to join the two tables
>>with a FULL OUTER JOIN.
>>
>>
>
>I'm confused. If the columns are NOT NULL, why isn't this a valid
>transformation of your original query?
>
>
>
>>select temp_max_60min,temp_dry_at_2m
>>from station s natural join
>>temp_dry_at_2m a full outer join temp_max_60min b using (station_id, timeobs)
>>where s.wmo_id=6065
>>and _a.timeobs='2004-1-1 0:0:0' and b._timeobs='2004-1-1 0:0:0'
>>and '2004-1-1 0:0:0' between s.startdate and s.enddate;
>>
>>
>
>Seems like it's not eliminating any rows that would otherwise succeed.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
>
--
Kim Bisgaard
Computer Department Phone: +45 3915 7562 (direct)
Danish Meteorological Institute Fax: +45 3915 7460 (division)
^ permalink raw reply [nested|flat] 12+ messages in thread
* full outer performance problem
@ 2005-06-08 09:37 Kim Bisgaard <kib+pg@dmi.dk>
2005-06-08 12:17 ` Re: full outer performance problem Bruno Wolff III <bruno@wolff.to>
2005-06-08 14:03 ` Re: full outer performance problem Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 12+ messages in thread
From: Kim Bisgaard @ 2005-06-08 09:37 UTC (permalink / raw)
To: pgsql-performance
Hi,
I'm having problems with the query optimizer and FULL OUTER JOIN on
PostgreSQL 7.4. I cannot get it to use my indexes with full outer joins.
I might be naive, but I think that it should be possible?
I have two BIG tables (virtually identical) with 3 NOT NULL columns
Station_id, TimeObs, Temp_XXXX, with unique indexes on (Station_id,
TimeObs) and valid ANALYSE (set statistics=100). I want to join the two
tables with a FULL OUTER JOIN.
When I specify the query as:
SELECT station_id, timeobs,temp_grass, temp_dry_at_2m
FROM temp_dry_at_2m a
FULL OUTER JOIN temp_grass b
USING (station_id, timeobs)
WHERE station_id = 52981
AND timeobs = '2004-1-1 0:0:0'
I get the correct results
station_id | timeobs | temp_grass | temp_dry_at_2m
------------+---------------------+------------+----------------
52944 | 2004-01-01 00:10:00 | | -1.1
(1 row)
BUT LOUSY performance, and the following EXPLAIN:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Full Join (cost=1542369.83..1618958.58 rows=6956994 width=32) (actual time=187176.408..201436.264 rows=1 loops=1)
Merge Cond: (("outer".station_id = "inner".station_id) AND ("outer".timeobs = "inner".timeobs))
Filter: ((COALESCE("outer".station_id, "inner".station_id) = 52981) AND (COALESCE("outer".timeobs, "inner".timeobs) = '2004-01-01 00:00:00'::timestamp without time zone))
-> Sort (cost=1207913.44..1225305.93 rows=6956994 width=16) (actual time=145748.253..153851.607 rows=6956994 loops=1)
Sort Key: a.station_id, a.timeobs
-> Seq Scan on temp_dry_at_2m a (cost=0.00..117549.94 rows=6956994 width=16) (actual time=0.049..54226.770 rows=6956994 loops=1)
-> Sort (cost=334456.38..340472.11 rows=2406292 width=16) (actual time=31668.876..34491.123 rows=2406292 loops=1)
Sort Key: b.station_id, b.timeobs
-> Seq Scan on temp_grass b (cost=0.00..40658.92 rows=2406292 width=16) (actual time=0.052..5484.489 rows=2406292 loops=1)
Total runtime: 201795.989 ms
(10 rows)
If I change the query (note the "b."s)
explain analyse SELECT b.station_id, b.timeobs,temp_grass, temp_dry_at_2m
FROM temp_dry_at_2m a
FULL OUTER JOIN temp_grass b
USING (station_id, timeobs)
WHERE b.station_id = 52981
AND b.timeobs = '2004-1-1 0:0:0'
I seem to destroy the FULL OUTER JOIN and get wrong results (nothing)
If I had happend to use "a.", and not "b.", I would have gotten correct
results (by accident).
The "a." variant gives this EXPLAIN:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..11.97 rows=1 width=20) (actual time=0.060..0.067 rows=1 loops=1)
-> Index Scan using temp_dry_at_2m_idx on temp_dry_at_2m a (cost=0.00..5.99 rows=1 width=16) (actual time=0.033..0.036 rows=1 loops=1)
Index Cond: ((station_id = 52981) AND (timeobs = '2004-01-01 00:00:00'::timestamp without time zone))
-> Index Scan using temp_grass_idx on temp_grass b (cost=0.00..5.96 rows=1 width=16) (actual time=0.018..0.021 rows=1 loops=1)
Index Cond: (("outer".station_id = b.station_id) AND ("outer".timeobs = b.timeobs))
Total runtime: 0.140 ms
(6 rows)
Why will PostgreSQL not use the same plan for both these queries - they
are virtually identical??
I have tried to formulate the problem with left joins, but this demands
from me that I know which table has all the values (and thus has to go
first), and in practice no such table excists.
TIA,
Kim Bisgaard.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: full outer performance problem
2005-06-08 09:37 full outer performance problem Kim Bisgaard <kib+pg@dmi.dk>
@ 2005-06-08 12:17 ` Bruno Wolff III <bruno@wolff.to>
2005-06-08 12:32 ` Re: full outer performance problem Kim Bisgaard <kib+pg@dmi.dk>
1 sibling, 1 reply; 12+ messages in thread
From: Bruno Wolff III @ 2005-06-08 12:17 UTC (permalink / raw)
To: Kim Bisgaard <kib+pg@dmi.dk>; +Cc: pgsql-performance
On Wed, Jun 08, 2005 at 11:37:40 +0200,
Kim Bisgaard <kib+pg@dmi.dk> wrote:
> Hi,
>
> I'm having problems with the query optimizer and FULL OUTER JOIN on
> PostgreSQL 7.4. I cannot get it to use my indexes with full outer joins.
> I might be naive, but I think that it should be possible?
>
> I have two BIG tables (virtually identical) with 3 NOT NULL columns
> Station_id, TimeObs, Temp_XXXX, with unique indexes on (Station_id,
> TimeObs) and valid ANALYSE (set statistics=100). I want to join the two
> tables with a FULL OUTER JOIN.
>
> When I specify the query as:
>
> SELECT station_id, timeobs,temp_grass, temp_dry_at_2m
> FROM temp_dry_at_2m a
> FULL OUTER JOIN temp_grass b
> USING (station_id, timeobs)
> WHERE station_id = 52981
> AND timeobs = '2004-1-1 0:0:0'
>
> I get the correct results
>
> station_id | timeobs | temp_grass | temp_dry_at_2m
> ------------+---------------------+------------+----------------
> 52944 | 2004-01-01 00:10:00 | | -1.1
> (1 row)
>
> BUT LOUSY performance, and the following EXPLAIN:
>
> QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Merge Full Join (cost=1542369.83..1618958.58 rows=6956994 width=32)
> (actual time=187176.408..201436.264 rows=1 loops=1)
> Merge Cond: (("outer".station_id = "inner".station_id) AND
> ("outer".timeobs = "inner".timeobs))
> Filter: ((COALESCE("outer".station_id, "inner".station_id) = 52981) AND
> (COALESCE("outer".timeobs, "inner".timeobs) = '2004-01-01
> 00:00:00'::timestamp without time zone))
> -> Sort (cost=1207913.44..1225305.93 rows=6956994 width=16) (actual
> time=145748.253..153851.607 rows=6956994 loops=1)
> Sort Key: a.station_id, a.timeobs
> -> Seq Scan on temp_dry_at_2m a (cost=0.00..117549.94
> rows=6956994 width=16) (actual time=0.049..54226.770 rows=6956994
> loops=1)
> -> Sort (cost=334456.38..340472.11 rows=2406292 width=16) (actual
> time=31668.876..34491.123 rows=2406292 loops=1)
> Sort Key: b.station_id, b.timeobs
> -> Seq Scan on temp_grass b (cost=0.00..40658.92 rows=2406292
> width=16) (actual time=0.052..5484.489 rows=2406292 loops=1)
> Total runtime: 201795.989 ms
> (10 rows)
Someone else will need to comment on why Postgres can't use a more
efficient plan. What I think will work for you is to restrict
the station_id and timeobs on each side and then do a full join.
You can try something like the sample query below (which hasn't been tested):
SELECT station_id, timeobs, temp_grass, temp_dry_at_2m
FROM
(SELECT station_id, timeobs, temp_dry_at_2m
FROM temp_dry_at_2m
WHERE
station_id = 52981
AND
timeobs = '2004-1-1 0:0:0') a
FULL OUTER JOIN
(SELECT station_id, timeobs, temp_grass
FROM temp_grass
WHERE
station_id = 52981
AND
timeobs = '2004-1-1 0:0:0') b
USING (station_id, timeobs)
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: full outer performance problem
2005-06-08 09:37 full outer performance problem Kim Bisgaard <kib+pg@dmi.dk>
2005-06-08 12:17 ` Re: full outer performance problem Bruno Wolff III <bruno@wolff.to>
@ 2005-06-08 12:32 ` Kim Bisgaard <kib+pg@dmi.dk>
2005-06-08 16:23 ` Re: full outer performance problem Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Kim Bisgaard @ 2005-06-08 12:32 UTC (permalink / raw)
To: pgsql-performance; +Cc: Kim Bisgaard <kib+pg@dmi.dk>
Hi Bruno,
Thanks for the moral support! I feel so too - but I am confident it will
show up soon.
W.r.t. your rewrite of the query, I get this "ERROR: could not devise a
query plan for the given query" but no further details - I will try google
Regards,
Kim.
Bruno Wolff III wrote:
>On Wed, Jun 08, 2005 at 11:37:40 +0200,
> Kim Bisgaard <kib+pg@dmi.dk> wrote:
>
>
>>Hi,
>>
>>I'm having problems with the query optimizer and FULL OUTER JOIN on
>>PostgreSQL 7.4. I cannot get it to use my indexes with full outer joins.
>>I might be naive, but I think that it should be possible?
>>
>>I have two BIG tables (virtually identical) with 3 NOT NULL columns
>>Station_id, TimeObs, Temp_XXXX, with unique indexes on (Station_id,
>>TimeObs) and valid ANALYSE (set statistics=100). I want to join the two
>>tables with a FULL OUTER JOIN.
>>
>>When I specify the query as:
>>
>>SELECT station_id, timeobs,temp_grass, temp_dry_at_2m
>> FROM temp_dry_at_2m a
>> FULL OUTER JOIN temp_grass b
>> USING (station_id, timeobs)
>> WHERE station_id = 52981
>> AND timeobs = '2004-1-1 0:0:0'
>>
>>I get the correct results
>>
>>station_id | timeobs | temp_grass | temp_dry_at_2m
>>------------+---------------------+------------+----------------
>> 52944 | 2004-01-01 00:10:00 | | -1.1
>>(1 row)
>>
>>BUT LOUSY performance, and the following EXPLAIN:
>>
>> QUERY PLAN
>>------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>>Merge Full Join (cost=1542369.83..1618958.58 rows=6956994 width=32)
>>(actual time=187176.408..201436.264 rows=1 loops=1)
>> Merge Cond: (("outer".station_id = "inner".station_id) AND
>> ("outer".timeobs = "inner".timeobs))
>> Filter: ((COALESCE("outer".station_id, "inner".station_id) = 52981) AND
>> (COALESCE("outer".timeobs, "inner".timeobs) = '2004-01-01
>> 00:00:00'::timestamp without time zone))
>> -> Sort (cost=1207913.44..1225305.93 rows=6956994 width=16) (actual
>> time=145748.253..153851.607 rows=6956994 loops=1)
>> Sort Key: a.station_id, a.timeobs
>> -> Seq Scan on temp_dry_at_2m a (cost=0.00..117549.94
>> rows=6956994 width=16) (actual time=0.049..54226.770 rows=6956994
>> loops=1)
>> -> Sort (cost=334456.38..340472.11 rows=2406292 width=16) (actual
>> time=31668.876..34491.123 rows=2406292 loops=1)
>> Sort Key: b.station_id, b.timeobs
>> -> Seq Scan on temp_grass b (cost=0.00..40658.92 rows=2406292
>> width=16) (actual time=0.052..5484.489 rows=2406292 loops=1)
>>Total runtime: 201795.989 ms
>>(10 rows)
>>
>>
>
>Someone else will need to comment on why Postgres can't use a more
>efficient plan. What I think will work for you is to restrict
>the station_id and timeobs on each side and then do a full join.
>You can try something like the sample query below (which hasn't been tested):
>SELECT station_id, timeobs, temp_grass, temp_dry_at_2m
> FROM
> (SELECT station_id, timeobs, temp_dry_at_2m
> FROM temp_dry_at_2m
> WHERE
> station_id = 52981
> AND
> timeobs = '2004-1-1 0:0:0') a
> FULL OUTER JOIN
> (SELECT station_id, timeobs, temp_grass
> FROM temp_grass
> WHERE
> station_id = 52981
> AND
> timeobs = '2004-1-1 0:0:0') b
> USING (station_id, timeobs)
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: full outer performance problem
2005-06-08 09:37 full outer performance problem Kim Bisgaard <kib+pg@dmi.dk>
2005-06-08 12:17 ` Re: full outer performance problem Bruno Wolff III <bruno@wolff.to>
2005-06-08 12:32 ` Re: full outer performance problem Kim Bisgaard <kib+pg@dmi.dk>
@ 2005-06-08 16:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-08 18:53 ` Re: full outer performance problem Kim Bisgaard <kib+pg@dmi.dk>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2005-06-08 16:23 UTC (permalink / raw)
To: Kim Bisgaard <kib+pg@dmi.dk>; +Cc: pgsql-performance
Kim Bisgaard <kib+pg@dmi.dk> writes:
> W.r.t. your rewrite of the query, I get this "ERROR: could not devise a
> query plan for the given query" but no further details - I will try google
Which PG version are you using again? That should be fixed in 7.4.3
and later.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: full outer performance problem
2005-06-08 09:37 full outer performance problem Kim Bisgaard <kib+pg@dmi.dk>
2005-06-08 12:17 ` Re: full outer performance problem Bruno Wolff III <bruno@wolff.to>
2005-06-08 12:32 ` Re: full outer performance problem Kim Bisgaard <kib+pg@dmi.dk>
2005-06-08 16:23 ` Re: full outer performance problem Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-06-08 18:53 ` Kim Bisgaard <kib+pg@dmi.dk>
0 siblings, 0 replies; 12+ messages in thread
From: Kim Bisgaard @ 2005-06-08 18:53 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Quoting Tom Lane <tgl@sss.pgh.pa.us>:
> Kim Bisgaard <kib+pg@dmi.dk> writes:
> > W.r.t. your rewrite of the query, I get this "ERROR: could not devise a
> > query plan for the given query" but no further details - I will try google
>
> Which PG version are you using again? That should be fixed in 7.4.3
> and later.
>
> regards, tom lane
>
Its 7.4.1. I am in the process (may take a while yet) of installing 8.0.3 on the
same hardware in order to have a parallel system. Time is a finite meassure :-)
I must admit I would rather have the first query perform, that have this
workaround function ;-)
Regards,
Kim.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: full outer performance problem
2005-06-08 09:37 full outer performance problem Kim Bisgaard <kib+pg@dmi.dk>
@ 2005-06-08 14:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-08 18:46 ` Re: full outer performance problem Kim Bisgaard <kib+pg@dmi.dk>
1 sibling, 1 reply; 12+ messages in thread
From: Tom Lane @ 2005-06-08 14:03 UTC (permalink / raw)
To: Kim Bisgaard <kib+pg@dmi.dk>; +Cc: pgsql-performance
Kim Bisgaard <kib+pg@dmi.dk> writes:
> SELECT station_id, timeobs,temp_grass, temp_dry_at_2m
> FROM temp_dry_at_2m a
> FULL OUTER JOIN temp_grass b
> USING (station_id, timeobs)
> WHERE station_id = 52981
> AND timeobs = '2004-1-1 0:0:0'
> explain analyse SELECT b.station_id, b.timeobs,temp_grass, temp_dry_at_2m
> FROM temp_dry_at_2m a
> FULL OUTER JOIN temp_grass b
> USING (station_id, timeobs)
> WHERE b.station_id = 52981
> AND b.timeobs = '2004-1-1 0:0:0'
> Why will PostgreSQL not use the same plan for both these queries - they
> are virtually identical??
Because they're semantically completely different. The second query is
effectively a RIGHT JOIN, because join rows in which b is all-null will
be thrown away by the WHERE. The optimizer sees this (note your second
plan doesn't use a Full Join step anywhere) and is able to produce a
much better plan. Full outer join is difficult to optimize, in part
because we have no choice but to use a merge join for it --- the other
join types don't support full join.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: full outer performance problem
2005-06-08 09:37 full outer performance problem Kim Bisgaard <kib+pg@dmi.dk>
2005-06-08 14:03 ` Re: full outer performance problem Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-06-08 18:46 ` Kim Bisgaard <kib+pg@dmi.dk>
0 siblings, 0 replies; 12+ messages in thread
From: Kim Bisgaard @ 2005-06-08 18:46 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Quoting Tom Lane <tgl@sss.pgh.pa.us>:
> Kim Bisgaard <kib+pg@dmi.dk> writes:
> > SELECT station_id, timeobs,temp_grass, temp_dry_at_2m
> > FROM temp_dry_at_2m a
> > FULL OUTER JOIN temp_grass b
> > USING (station_id, timeobs)
> > WHERE station_id = 52981
> > AND timeobs = '2004-1-1 0:0:0'
>
> > explain analyse SELECT b.station_id, b.timeobs,temp_grass, temp_dry_at_2m
> > FROM temp_dry_at_2m a
> > FULL OUTER JOIN temp_grass b
> > USING (station_id, timeobs)
> > WHERE b.station_id = 52981
> > AND b.timeobs = '2004-1-1 0:0:0'
>
> > Why will PostgreSQL not use the same plan for both these queries - they
> > are virtually identical??
>
> Because they're semantically completely different. The second query is
> effectively a RIGHT JOIN, because join rows in which b is all-null will
> be thrown away by the WHERE. The optimizer sees this (note your second
> plan doesn't use a Full Join step anywhere) and is able to produce a
> much better plan. Full outer join is difficult to optimize, in part
> because we have no choice but to use a merge join for it --- the other
> join types don't support full join.
>
> regards, tom lane
>
Yes I am aware that they are not "identical", they also give different results,
but the data nessesary to compute the results is (0-2 rows, 0-1 row from each
table), and thus ideally have the potential to have similar performance - to my
head anyway, but I may not have grasped the complete picture yet :-)
Regards,
Kim.
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2005-06-08 18:53 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-10 09:03 full outer performance problem Kim Bisgaard <kib+pg@dmi.dk>
2005-05-10 14:28 ` John A Meinel <john@arbash-meinel.com>
2005-05-11 08:20 ` Kim Bisgaard <kib@dmi.dk>
2005-05-10 14:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-05-11 07:05 ` Kim Bisgaard <kib@dmi.dk>
2005-06-08 09:37 full outer performance problem Kim Bisgaard <kib+pg@dmi.dk>
2005-06-08 12:17 ` Bruno Wolff III <bruno@wolff.to>
2005-06-08 12:32 ` Kim Bisgaard <kib+pg@dmi.dk>
2005-06-08 16:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-08 18:53 ` Kim Bisgaard <kib+pg@dmi.dk>
2005-06-08 14:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-08 18:46 ` Kim Bisgaard <kib+pg@dmi.dk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox