pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedJoin performance
12+ messages / 5 participants
[nested] [flat]
* Join performance
@ 2000-12-05 09:42 Koen Antonissen <koen@v3.com>
0 siblings, 0 replies; 12+ messages in thread
From: Koen Antonissen @ 2000-12-05 09:42 UTC (permalink / raw)
To: pgsql-sql
Hi there,
I'm having some problems with the performance on queries including a join.
for example:
SELECT members_data.pwd FROM emails,members_data WHERE emails.email =
'koen@v3.com' AND emails.uin = members_data.uin;
is a lot slower then extracting the join into 2 seperate queries like:
SELECT emails.uin FROM emails WHERE lower(emails.email) = 'koen@v3.com';
--and use the result in the next query ($db_result)
SELECT members_data.pwd FROM members_data WHERE members_data.uin =
$db_result;
Now the login routine is MUCH faster ;-) , but performance problems remain
when the the result is more than 1 record (Unlike a unique emailaddress...)
Is there anyone out there who has ideas how to write faster queries
including tablejoins?
I already tried Inner Join, Natural Join and Join On, wich didn't seem
affect the performance in any way...
Kind regards,
Koen Antonissen
koen@trilab.com
www.trilab.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* JOIN performance
@ 2004-09-20 19:24 Dean Gibson (DB Administrator) <postgresql3@ultimeth.com>
0 siblings, 2 replies; 12+ messages in thread
From: Dean Gibson (DB Administrator) @ 2004-09-20 19:24 UTC (permalink / raw)
To: pgsql-sql
I have a view that when used, is slow:
CREATE VIEW "Data".genlic_a4avail AS
SELECT
genlic_a4.*,
last_action_date,
end_date,
canceled
FROM genlic_a4
LEFT JOIN lic_hd USING( sys_id )
WHERE status != 'A';
Here is the EXPLAIN output:
Merge Join (cost=155360.47..159965.70 rows=13063 width=75)
Merge Cond: ("outer".sys_id = "inner".sys_id)
-> Sort (cost=3912.51..3916.48 rows=1589 width=62)
Sort Key: "_GenLicGroupA4".sys_id
-> Nested Loop (cost=0.00..3828.04 rows=1589 width=62)
Join Filter: ("outer".callsign ~ ("inner".pattern)::text)
-> Seq Scan on "_GenLicGroupA4" (cost=0.00..1667.40
rows=1589 width=21)
Filter: ((status <> 'R'::bpchar) AND (status <>
'A'::bpchar) AND (geo_region = 12))
-> Seq Scan on "_GeoRestrict" (cost=0.00..1.16 rows=16
width=41)
-> Sort (cost=262032.96..264249.96 rows=886799 width=72)
Sort Key: lic_hd.sys_id
-> Subquery Scan lic_hd (cost=0.00..24529.99 rows=886799 width=72)
-> Seq Scan on "_LicHD" (cost=0.00..24529.99 rows=886799
width=72)
If I change the view to this:
CREATE VIEW "Data".genlic_a4avail AS
SELECT
genlic_a4.*,
(SELECT last_action_date FROM lic_hd WHERE sys_id =
genlic_a4.sys_id LIMIT 1) AS
last_action_date,
(SELECT end_date FROM lic_hd WHERE sys_id =
genlic_a4.sys_id LIMIT 1) AS
end_date,
(SELECT canceled FROM lic_hd WHERE sys_id =
genlic_a4.sys_id LIMIT 1) AS
canceled
FROM genlic_a4
WHERE status != 'A';
Then the performance is MUCH better:
Subquery Scan genlic_a4avail (cost=0.00..3828.04 rows=1589 width=62)
-> Nested Loop (cost=0.00..3828.04 rows=1589 width=62)
Join Filter: ("outer".callsign ~ ("inner".pattern)::text)
-> Seq Scan on "_GenLicGroupA4" (cost=0.00..1667.40 rows=1589
width=21)
Filter: ((status <> 'R'::bpchar) AND (status <>
'A'::bpchar) AND (geo_region = 12))
-> Seq Scan on "_GeoRestrict" (cost=0.00..1.16 rows=16 width=41)
SubPlan
-> Limit (cost=0.00..3.01 rows=1 width=4)
-> Index Scan using "_LicHD_pkey" on
"_LicHD" (cost=0.00..3.01 rows=1 width=4)
Index Cond: (unique_system_identifier = $0)
-> Limit (cost=0.00..3.01 rows=1 width=8)
-> Index Scan using "_LicHD_pkey" on
"_LicHD" (cost=0.00..3.01 rows=1 width=8)
Index Cond: (unique_system_identifier = $0)
-> Limit (cost=0.00..3.01 rows=1 width=8)
-> Index Scan using "_LicHD_pkey" on
"_LicHD" (cost=0.00..3.01 rows=1 width=8)
Index Cond: (unique_system_identifier = $0)
Note that genlic_a4 is small (4519), and lic_hd is large (886799), and
lic_hd has sys_id as its PRIMARY KEY.
Is there a better way to write the LEFT JOIN so as to achieve the
performance of the second VIEW without the clumsiness of the three (SELECT
... LIMIT 1) ???
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: JOIN performance
@ 2004-09-20 23:06 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dean Gibson (DB Administrator) <postgresql3@ultimeth.com>
1 sibling, 0 replies; 12+ messages in thread
From: Tom Lane @ 2004-09-20 23:06 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql3@ultimeth.com>; +Cc: pgsql-sql
"Dean Gibson (DB Administrator)" <postgresql3@ultimeth.com> writes:
> I have a view that when used, is slow:
It's obvious that you haven't given us anything remotely like full
information here. AFAICT the "tables" in your view are actually
other views, plus it looks like your call to the view is a query
that adds some other conditions you didn't mention. (Or are those
conditions coming from the sub-views? Hard to tell.) If you want
useful help you need to be more complete.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: JOIN performance
@ 2004-09-21 00:10 Dean Gibson (DB Administrator) <postgresql3@ultimeth.com>
parent: Dean Gibson (DB Administrator) <postgresql3@ultimeth.com>
1 sibling, 2 replies; 12+ messages in thread
From: Dean Gibson (DB Administrator) @ 2004-09-21 00:10 UTC (permalink / raw)
To: pgsql-sql
Tom Lane wrote on 2004-09-20 16:06:
>"Dean Gibson (DB Administrator)" <postgresql3@ultimeth.com> writes:
> > I have a view that when used, is slow:
>
>... If you want useful help you need to be more complete.
I use views to "hide" tables so that I can populate new tables and then
atomically switch to them with "CREATE OR REPLACE ...". Here is the same
data with the raw tables:
=> explain select * from "20040920_070010"."_GenLicGroupA4" AS x LEFT JOIN
"20040919_070713"."_LicHD" AS y ON x.sys_id =
y.unique_system_identifier;
QUERY
PLAN
-------------------------------------------------------------------------------------------------
Merge Join (cost=5235.14..35123.51 rows=43680 width=365)
Merge Cond: ("outer".unique_system_identifier = "inner".sys_id)
-> Index Scan using "_LicHD_pkey" on "_LicHD" y (cost=0.00..27361.79
rows=886799 width=344)
-> Sort (cost=5235.14..5344.34 rows=43680 width=21)
Sort Key: x.sys_id
-> Seq Scan on "_GenLicGroupA4" x (cost=0.00..1339.80
rows=43680 width=21)
Using first level views, as mentioned above, the results are the same:
=> explain select * from "Base"."GenLicGroupA4" AS x LEFT JOIN
"Base"."LicHD" AS y ON x.sys_id =
y.unique_system_identifier;
QUERY
PLAN
-----------------------------------------------------------------------------------------------
Merge Join (cost=5235.14..35123.51 rows=43680 width=365)
Merge Cond: ("outer".unique_system_identifier = "inner".sys_id)
-> Index Scan using "_LicHD_pkey" on "_LicHD" (cost=0.00..27361.79
rows=886799 width=344)
-> Sort (cost=5235.14..5344.34 rows=43680 width=21)
Sort Key: "_GenLicGroupA4".sys_id
-> Seq Scan on "_GenLicGroupA4" (cost=0.00..1339.80 rows=43680
width=21)
However, when I introduce a second-level view for the second table of:
CREATE VIEW "Data".lic_hd AS
SELECT
unique_system_identifier AS sys_id,
callsign AS callsign,
uls_file_number AS
uls_file_num,
applicant_type_code AS
applicant_type,
radio_service_code AS
radio_service,
license_status AS
license_status,
grant_date AS grant_date,
effective_date AS
effective_date,
cancellation_date AS
cancel_date,
expired_date AS
expire_date,
last_action_date AS
last_action_date,
CASE WHEN cancellation_date < expired_date
THEN cancellation_date
ELSE expired_date
END AS end_date,
cancellation_date < expired_date AS canceled
FROM "Base"."LicHD";
And then change the query to use it, I get:
=> explain select * from "Base"."GenLicGroupA4" AS x LEFT JOIN lic_hd AS y
ON x.sys_id = y.sys_id;
QUERY
PLAN
------------------------------------------------------------------------------------
Merge Join (cost=280258.11..289399.92 rows=359154 width=98)
Merge Cond: ("outer".sys_id = "inner".sys_id)
-> Sort (cost=5235.14..5344.34 rows=43680 width=21)
Sort Key: "_GenLicGroupA4".sys_id
-> Seq Scan on "_GenLicGroupA4" (cost=0.00..1339.80 rows=43680
width=21)
-> Sort (cost=262032.96..264249.96 rows=886799 width=72)
Sort Key: y.sys_id
-> Subquery Scan y (cost=0.00..24529.99 rows=886799 width=72)
-> Seq Scan on "_LicHD" (cost=0.00..24529.99 rows=886799
width=72)
Note that the scan on _LicHD is now sequential. If I change the above view
to remove the last two columns, I get:
QUERY
PLAN
------------------------------------------------------------------------------------
Merge Join (cost=5235.14..35123.51 rows=43680 width=93)
Merge Cond: ("outer".unique_system_identifier = "inner".sys_id)
-> Index Scan using "_LicHD_pkey" on "_LicHD" (cost=0.00..27361.79
rows=886799 width=72)
-> Sort (cost=5235.14..5344.34 rows=43680 width=21)
Sort Key: x.sys_id
-> Seq Scan on "_GenLicGroupA4" x (cost=0.00..1339.80
rows=43680 width=21)
Which is back to my original (good) performance.
Question: why do the last two column definitions in the second VIEW change
the scan on _LicHD from indexed to sequential ??
-- Dean
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: JOIN performance
@ 2004-09-21 00:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dean Gibson (DB Administrator) <postgresql3@ultimeth.com>
1 sibling, 1 reply; 12+ messages in thread
From: Tom Lane @ 2004-09-21 00:54 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql3@ultimeth.com>; +Cc: pgsql-sql
"Dean Gibson (DB Administrator)" <postgresql3@ultimeth.com> writes:
> Question: why do the last two column definitions in the second VIEW change
> the scan on _LicHD from indexed to sequential ??
It's the CASE that's getting you. The poor plan is basically because
the sub-view isn't getting "flattened" into the upper query, and so it's
not possible to choose a plan for it that's dependent on the upper query
context. And the reason it's not getting flattened is that subselects
that are on the nullable side of an outer join can't be flattened unless
they have nullable targetlists --- otherwise the results might not go to
NULL when they are supposed to. A CASE construct is always going to be
treated as non-nullable.
Fixing this properly is a research project, and I haven't thought of any
quick-and-dirty hacks that aren't too ugly to consider :-(
In the meantime, you could easily replace that CASE construct with a
min() function that's declared strict. I think date_smaller would
do nicely, assuming the columns are actually of type date.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: JOIN performance
@ 2004-09-21 01:41 Dean Gibson (DB Administrator) <postgresql3@ultimeth.com>
parent: Dean Gibson (DB Administrator) <postgresql3@ultimeth.com>
1 sibling, 3 replies; 12+ messages in thread
From: Dean Gibson (DB Administrator) @ 2004-09-21 01:41 UTC (permalink / raw)
To: pgsql-sql
Ahh, that explains why a "plain" JOIN (which doesn't do what I need) gave
much better performance than the LEFT JOIN.
I could ask why a CASE statement is always non-nullable, but I don't think
the answer would help be solve my problem. <grin> I suppose it's that even
though my particular CASE statement has WHEN/ELSE values that come from the
nullable side of the JOIN, in general that's not true ...
Okay, now for my big question: I searched high and low for a function that
would return the minimum of two dates, and found none. Now you come up
with "date_smaller", which works fine (as does "date_larger"), but where
are those documented? More importantly, where are other functions like
them documented?
-- Dean
ps: Who dreamt up the names "date_smaller" and "date_larger" ??? Much more
intuitive are "min_date" and "max_date".
pps: I realize that "date_smaller" isn't exactly equivalent to my CASE
statement; a NULL value for one of the CASE operands causes the result of
the ELSE clause to be returned, whereas "date_smaller" just returns NULL in
that case. In my data, that's significant. I suppose that COALESCE has
the same problem as CASE ...
Tom Lane wrote on 2004-09-20 17:54:
>"Dean Gibson (DB Administrator)" <postgresql3@ultimeth.com> writes:
>Question: Why do the last two column definitions in the second VIEW
>change the scan on _LicHD from indexed to sequential ??
>
>It's the CASE that's getting you. The poor plan is basically because the
>sub-view isn't getting "flattened" into the upper query, and so it's not
>possible to choose a plan for it that's dependent on the upper query
>context. And the reason it's not getting flattened is that subselects
>that are on the nullable side of an outer join can't be flattened unless
>they have nullable targetlists --- otherwise the results might not go to
>NULL when they are supposed to. A CASE construct is always going to be
>treated as non-nullable.
>
>Fixing this properly is a research project, and I haven't thought of any
>quick-and-dirty hacks that aren't too ugly to consider :-(
>
>In the meantime, you could easily replace that CASE construct with a min()
>function that's declared strict. I think date_smaller would do nicely,
>assuming the columns are actually of type date.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: JOIN performance
@ 2004-09-21 04:09 Chester Kustarz <chester@arbor.net>
parent: Dean Gibson (DB Administrator) <postgresql3@ultimeth.com>
2 siblings, 0 replies; 12+ messages in thread
From: Chester Kustarz @ 2004-09-21 04:09 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql3@ultimeth.com>; +Cc: pgsql-sql
On Mon, 20 Sep 2004, Dean Gibson (DB Administrator) wrote:
> Okay, now for my big question: I searched high and low for a function that
> would return the minimum of two dates, and found none. Now you come up
> with "date_smaller", which works fine (as does "date_larger"), but where
> are those documented? More importantly, where are other functions like
> them documented?
You can make them:
http://www.postgresql.org/docs/7.4/interactive/server-programming.html
"strict" means it will return NULL when the input is NULL.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: JOIN performance
@ 2004-09-21 04:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dean Gibson (DB Administrator) <postgresql3@ultimeth.com>
2 siblings, 0 replies; 12+ messages in thread
From: Tom Lane @ 2004-09-21 04:10 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql3@ultimeth.com>; +Cc: pgsql-sql
"Dean Gibson (DB Administrator)" <postgresql3@ultimeth.com> writes:
> I could ask why a CASE statement is always non-nullable, but I don't think
> the answer would help be solve my problem. <grin> I suppose it's that even
> though my particular CASE statement has WHEN/ELSE values that come from the
> nullable side of the JOIN, in general that's not true ...
Right, the code just sees CASE and barfs. A finer grain of analysis
could conclude that this CASE is actually safe, but I'm unconvinced that
it's worth doing. (Feel free to pursue this on -hackers if you care.)
> Okay, now for my big question: I searched high and low for a function that
> would return the minimum of two dates, and found none. Now you come up
> with "date_smaller", which works fine (as does "date_larger"), but where
> are those documented?
They aren't; they are actually only intended as support functions for
MIN (resp. MAX) on dates. But they're there, and there's nothing to
stop you using 'em.
(You do of course realize that you could have implemented these
functions for yourself in a one-liner sql or plpgsql function. Finding
the function in the system saves you a few minutes at most.)
> ps: Who dreamt up the names "date_smaller" and "date_larger" ???
[ shrug... ] Some now-forgotten grad student at Berkeley. All the
support functions for MIN and MAX are named 'foosmaller' and
'foolarger'. Try
select aggtransfn from pg_aggregate, pg_proc
where pg_proc.oid = aggfnoid and proname = 'min';
> pps: I realize that "date_smaller" isn't exactly equivalent to my CASE
> statement; a NULL value for one of the CASE operands causes the result of
> the ELSE clause to be returned, whereas "date_smaller" just returns NULL in
> that case. In my data, that's significant.
Well, in that case you may have a problem here. The point of the test
in question is that the expression has to return NULL if *either* input
is null. Now it's true that the LEFT JOIN only cares that it goes to
NULL when *both* inputs are NULL, but we don't have any way to declare
that particular property of a function. To make the planner happy you
will have to declare the function as STRICT, which will force it to
behave in the first fashion.
> I suppose that COALESCE has the same problem as CASE ...
Yup, of course.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: JOIN performance
@ 2004-09-21 04:41 Dean Gibson (DB Administrator) <postgresql3@ultimeth.com>
parent: Dean Gibson (DB Administrator) <postgresql3@ultimeth.com>
2 siblings, 2 replies; 12+ messages in thread
From: Dean Gibson (DB Administrator) @ 2004-09-21 04:41 UTC (permalink / raw)
To: pgsql-sql
It turns out that even when I removed the CASE statement from the VIEW, the
performance problem remained. I had to remove the conditional as well from
the VIEW.
To refresh your memory, there was this line in the VIEW (said VIEW being
the subject of the LEFT JOIN):
cancel_date < expire_date AS canceled,
(cancel_date & expire_date are DATEs in the base view)
When I removed the above line from the VIEW, the performance problem
disappeared.
Do you know why? I'm running 7.3.4, by the way.
-- Dean
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: JOIN performance
@ 2004-09-21 05:19 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dean Gibson (DB Administrator) <postgresql3@ultimeth.com>
1 sibling, 0 replies; 12+ messages in thread
From: Tom Lane @ 2004-09-21 05:19 UTC (permalink / raw)
To: Dean Gibson (DB Administrator) <postgresql3@ultimeth.com>; +Cc: pgsql-sql
"Dean Gibson (DB Administrator)" <postgresql3@ultimeth.com> writes:
> It turns out that even when I removed the CASE statement from the VIEW, the
> performance problem remained. I had to remove the conditional as well from
> the VIEW.
> cancel_date < expire_date AS canceled,
Yuck :-(
> Do you know why? I'm running 7.3.4, by the way.
7.3 is even stupider than 7.4, is why ...
In 7.3 only a view whose targetlist consists of simple column references
can be pulled up into the nullable side of an outer join.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: JOIN performance
@ 2004-09-21 06:24 Dean Gibson (DB Administrator) <postgresql3@ultimeth.com>
parent: Dean Gibson (DB Administrator) <postgresql3@ultimeth.com>
1 sibling, 0 replies; 12+ messages in thread
From: Dean Gibson (DB Administrator) @ 2004-09-21 06:24 UTC (permalink / raw)
To: pgsql-sql; +Cc: pgsql-sql
Tom Lane wrote on 2004-09-20 22:19:
>In 7.3 only a view whose targetlist consists of simple column references
>can be pulled up into the nullable side of an outer join.
OK, well you dragged me kicking and screaming into writing and 'end_date'
function (IMMUTABLE but non-STRICT). I then use that in the JOINed VIEW
(and other strategic places) rather than in the subject of the LEFT JOIN,
and that works with no performance problems.
Since we mentioned PostgreSQL versions, one of the things I'd like to see
in v8.0 is support for "DELETE FROM ... AS ..." and "UPDATE ... AS
...". For example, I need to do things like:
DELETE FROM xxx AS x WHERE field > xxx.field;
In other words, a DELETE that has criteria similar to a self JOIN.
Right now, I do this via:
CREATE VIEW yyy AS SELECT * FROM xxx;
DELETE FROM xxx WHERE field > yyy.field;
And that works fine.
But it's probably too late for v8.0 ...
-- Dean
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: JOIN performance
@ 2004-09-21 18:26 Greg Stark <gsstark@mit.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 12+ messages in thread
From: Greg Stark @ 2004-09-21 18:26 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Dean Gibson (DB Administrator) <postgresql3@ultimeth.com>; pgsql-sql
Tom Lane <tgl@sss.pgh.pa.us> writes:
> Fixing this properly is a research project, and I haven't thought of any
> quick-and-dirty hacks that aren't too ugly to consider :-(
Just thinking out loud here. Instead of trying to peek inside the CASE
couldn't the optimizer just wrap the non-strict expression in a conditional
that tests whether the row was found?
--
greg
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2004-09-21 18:26 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-12-05 09:42 Join performance Koen Antonissen <koen@v3.com>
2004-09-20 19:24 JOIN performance Dean Gibson (DB Administrator) <postgresql3@ultimeth.com>
2004-09-20 23:06 ` Re: JOIN performance Tom Lane <tgl@sss.pgh.pa.us>
2004-09-21 00:10 ` Re: JOIN performance Dean Gibson (DB Administrator) <postgresql3@ultimeth.com>
2004-09-21 00:54 ` Re: JOIN performance Tom Lane <tgl@sss.pgh.pa.us>
2004-09-21 18:26 ` Re: JOIN performance Greg Stark <gsstark@mit.edu>
2004-09-21 01:41 ` Re: JOIN performance Dean Gibson (DB Administrator) <postgresql3@ultimeth.com>
2004-09-21 04:09 ` Re: JOIN performance Chester Kustarz <chester@arbor.net>
2004-09-21 04:10 ` Re: JOIN performance Tom Lane <tgl@sss.pgh.pa.us>
2004-09-21 04:41 ` Re: JOIN performance Dean Gibson (DB Administrator) <postgresql3@ultimeth.com>
2004-09-21 05:19 ` Re: JOIN performance Tom Lane <tgl@sss.pgh.pa.us>
2004-09-21 06:24 ` Re: JOIN performance Dean Gibson (DB Administrator) <postgresql3@ultimeth.com>
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