pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIS NOT NULL and LEFT JOIN
9+ messages / 4 participants
[nested] [flat]
* IS NOT NULL and LEFT JOIN
@ 2014-10-19 04:10 Laurent Martelli <laurent.martelli@enercoop.org>
0 siblings, 1 reply; 9+ messages in thread
From: Laurent Martelli @ 2014-10-19 04:10 UTC (permalink / raw)
To: pgsql-performance
Hello there,
I have a strange query plan involving an IS NOT NULL and a LEFT JOIN.
I grant you that the query can be written without the JOIN on
user_user_info,
but it is generated like this by hibernate. Just changing the IS NOT
NULL condition
to the other side of useless JOIN makes a big difference in the query plan :
-- THE BAD ONE : given the selectivity on c.name and c.email, barely
more than one row will ever be returned
explain analyze select c.*
from contact_contact c
left outer join user_user_info u on c.user_info=u.id
left outer join contact_address a on c.address=a.id
where lower(c.name)='martelli'
and c.email='dsfze@ezrfz.com' or u.id is not null;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Hash Left Join (cost=1.83..2246.76 rows=59412 width=4012) (actual
time=53.645..53.645 rows=0 loops=1)
Hash Cond: (c.user_info = u.id)
Filter: (((lower((c.name)::text) = 'martelli'::text) AND
((c.email)::text = 'dsfze@ezrfz.com'::text)) OR (u.id IS NOT NULL))
Rows Removed by Filter: 58247
-> Seq Scan on contact_contact c (cost=0.00..2022.12 rows=59412
width=4012) (actual time=0.007..6.892 rows=58247 loops=1)
-> Hash (cost=1.37..1.37 rows=37 width=8) (actual
time=0.029..0.029 rows=37 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 2kB
-> Seq Scan on user_user_info u (cost=0.00..1.37 rows=37
width=8) (actual time=0.004..0.015 rows=37 loops=1)
Planning time: 0.790 ms
Execution time: 53.712 ms
-- THE GOOD ONE (test IS NOT NULL on contact0_.user_info instead of
userinfo1_.id)
explain analyze select c.*
from contact_contact c
left outer join user_user_info u on c.user_info=u.id
left outer join contact_address a on c.address=a.id
where lower(c.name)='martelli'
and c.email='dsfze@ezrfz.com' or c.user_info is not null;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on contact_contact c (cost=8.60..16.41 rows=1
width=4012) (actual time=0.037..0.037 rows=0 loops=1)
Recheck Cond: (((email)::text = 'dsfze@ezrfz.com'::text) OR
(user_info IS NOT NULL))
Filter: (((lower((name)::text) = 'martelli'::text) AND
((email)::text = 'dsfze@ezrfz.com'::text)) OR (user_info IS NOT NULL))
-> BitmapOr (cost=8.60..8.60 rows=2 width=0) (actual
time=0.034..0.034 rows=0 loops=1)
-> Bitmap Index Scan on idx_contact_email (cost=0.00..4.30
rows=2 width=0) (actual time=0.027..0.027 rows=0 loops=1)
Index Cond: ((email)::text = 'dsfze@ezrfz.com'::text)
-> Bitmap Index Scan on contact_contact_user_info_idx
(cost=0.00..4.30 rows=1 width=0) (actual time=0.007..0.007 rows=0 loops=1)
Index Cond: (user_info IS NOT NULL)
Planning time: 0.602 ms
Execution time: 0.118 ms
My tables are as follow, and I use postgres 9.4 :
Table « public.contact_contact »
Colonne | Type | Modificateurs | Stockage | Cible de statistiques | Description
------------------------+-----------------------------+---------------+----------+-----------------------+-------------
id | bigint | non NULL | plain | |
archived | boolean | | plain | |
version | integer | | plain | |
created_on | timestamp without time zone | | plain | |
updated_on | timestamp without time zone | | plain | |
actor_ref | character varying(255) | | extended | |
addressl1 | character varying(255) | | extended | |
comment | text | | extended | |
contact_partner_ok | boolean | | plain | |
date_of_birth | date | | plain | |
email | character varying(255) | | extended | |
email_pro | character varying(255) | | extended | |
fax | character varying(255) | | extended | |
first_name | character varying(255) | | extended | |
fixed_phone1 | character varying(255) | | extended | |
fixed_phone2 | character varying(255) | | extended | |
fixed_phone_pro | character varying(255) | | extended | |
import_key1 | character varying(255) | | extended | |
import_key2 | character varying(255) | | extended | |
koala_id | character varying(255) | | extended | |
mobile_phone_perso | character varying(255) | | extended | |
mobile_phone_pro | character varying(255) | | extended | |
name | character varying(255) | non NULL | extended | |
ola_email | character varying(255) | | extended | |
ola_phone | character varying(255) | | extended | |
person_category_select | character varying(255) | | extended | |
web_site | character varying(255) | | extended | |
year_of_birth | integer | | plain | |
created_by | bigint | | plain | |
updated_by | bigint | | plain | |
action_event_source | bigint | | plain | |
address | bigint | | plain | |
address_pro | bigint | | plain | |
jobtitle | bigint | | plain | |
merged_with | bigint | | plain | |
nationality_country | bigint | | plain | |
origin | bigint | | plain | |
place_of_birth_address | bigint | | plain | |
title | bigint | | plain | |
user_info | bigint | | plain | |
import_origin | character varying(255) | | extended | |
duplicates | bigint | | plain | |
Index :
"contact_contact_pkey" PRIMARY KEY, btree (id)
"uk_bx19539x7h0y0w4p4uw9gnqbo" UNIQUE CONSTRAINT, btree (koala_id)
"uk_vg25de8jcu18m89o9dy2n4fe" UNIQUE CONSTRAINT, btree (import_key1)
"contact_contact_action_event_source_idx" btree (action_event_source)
"contact_contact_address_idx" btree (address)
"contact_contact_address_l1_idx" btree (addressl1)
"contact_contact_address_pro_idx" btree (address_pro)
"contact_contact_jobtitle_idx" btree (jobtitle)
"contact_contact_merged_with_idx" btree (merged_with)
"contact_contact_name_idx" btree (name)
"contact_contact_nationality_country_idx" btree (nationality_country)
"contact_contact_origin_idx" btree (origin)
"contact_contact_place_of_birth_address_idx" btree (place_of_birth_address)
"contact_contact_title_idx" btree (title)
"contact_contact_user_info_idx" btree (user_info)
"idx_contact_email" btree (email)
"idx_contact_lower_name" btree (lower(name::text))
"idx_contact_search_name" btree (lower(name::text), lower(first_name::text))
Contraintes de clés étrangères :
"fk_8dj7rw3jrdxk4vxbi6vony0ne" FOREIGN KEY (created_by) REFERENCES auth_user(id)
"fk_9s1dhwrvw6lq74fvty6oj2wc5" FOREIGN KEY (address_pro) REFERENCES contact_address(id)
"fk_9wjsgh8lt5ixbshx9pjwmjtk1" FOREIGN KEY (origin) REFERENCES crm_origin(id)
"fk_ad53x8tdando1w1jdlyxcop9v" FOREIGN KEY (duplicates) REFERENCES contact_contact(id)
"fk_edusucr1gdfj99vtm0a70gggg" FOREIGN KEY (title) REFERENCES contact_title(id)
"fk_g7u75rjd754m7evn2alckjvka" FOREIGN KEY (merged_with) REFERENCES contact_contact(id)
"fk_j72hkuq0337v6utjbf85hhvxg" FOREIGN KEY (action_event_source) REFERENCES crm_action_event_source(id)
"fk_k73mcu7swia6uf6qpp4v6lwxf" FOREIGN KEY (updated_by) REFERENCES auth_user(id)
"fk_mvpl7wudcdqgitmmsd900od97" FOREIGN KEY (place_of_birth_address) REFERENCES contact_address(id)
"fk_onriw4jpgeuvhfk827amxry8k" FOREIGN KEY (address) REFERENCES contact_address(id)
"fk_rpkvno8705gap9ejj4wnnb7hl" FOREIGN KEY (nationality_country) REFERENCES territory_country(id)
"fk_s9fsy33u5a9ke8wee9mc2vpsx" FOREIGN KEY (user_info) REFERENCES user_user_info(id)
"fk_t8uexb8lmgaftjsnn63eoty90" FOREIGN KEY (jobtitle) REFERENCES contact_jobtitle(id)
coopener=# \d+ user_user_info
Table « public.user_user_info »
Colonne | Type | Modificateurs | Stockage | Cible de statistiques | Description
-----------------+-----------------------------+---------------+----------+-----------------------+-------------
id | bigint | non NULL | plain | |
archived | boolean | | plain | |
version | integer | | plain | |
created_on | timestamp without time zone | | plain | |
updated_on | timestamp without time zone | | plain | |
full_name | character varying(255) | | extended | |
import_key | character varying(255) | | extended | |
import_username | character varying(255) | | extended | |
today | timestamp without time zone | | plain | |
user_system_ok | boolean | | plain | |
created_by | bigint | | plain | |
updated_by | bigint | | plain | |
active_company | bigint | | plain | |
agency | bigint | | plain | |
internal_user | bigint | non NULL | plain | |
Index :
"user_user_info_pkey" PRIMARY KEY, btree (id)
"uk_99o17944ddytysui6b05lxyb2" UNIQUE CONSTRAINT, btree (import_key)
"uk_cqgrw75h35ts19uixn03rkjsu" UNIQUE CONSTRAINT, btree (internal_user)
"uk_jtsvu4r7s12nnh9o2sloqyqv4" UNIQUE CONSTRAINT, btree (import_username)
"user_user_info_active_company_idx" btree (active_company)
"user_user_info_agency_idx" btree (agency)
"user_user_info_full_name_idx" btree (full_name)
Contraintes de clés étrangères :
"fk_cojxp4r7d8n2l135gy4xa4vak" FOREIGN KEY (active_company) REFERENCES contact_company(id)
"fk_cqgrw75h35ts19uixn03rkjsu" FOREIGN KEY (internal_user) REFERENCES auth_user(id)
"fk_k3riohsx7jrhxkxdmxyeqflq1" FOREIGN KEY (updated_by) REFERENCES auth_user(id)
"fk_r3e16hs6puibteaby3rk42yg0" FOREIGN KEY (created_by) REFERENCES auth_user(id)
"fk_t389sdkhi9owy0xbhec2nqp5w" FOREIGN KEY (agency) REFERENCES contact_agency(id)
coopener=# \d+ contact_address
Table « public.contact_address »
Colonne | Type | Modificateurs | Stockage | Cible de statistiques | Description
----------------------+-----------------------------+---------------+----------+-----------------------+-------------
id | bigint | non NULL | plain | |
archived | boolean | | plain | |
version | integer | | plain | |
created_on | timestamp without time zone | | plain | |
updated_on | timestamp without time zone | | plain | |
addressl2 | character varying(255) | | extended | |
addressl3 | character varying(255) | | extended | |
addressl4 | character varying(255) | | extended | |
addressl5 | character varying(255) | | extended | |
addressl6 | character varying(255) | | extended | |
certified_ok | boolean | | plain | |
consumption_place_ok | boolean | | plain | |
full_name | character varying(255) | | extended | |
insee_code | character varying(255) | | extended | |
koala_id | character varying(255) | | extended | |
created_by | bigint | | plain | |
updated_by | bigint | | plain | |
addressl7country | bigint | | plain | |
commune | bigint | | plain | |
Index :
"contact_address_pkey" PRIMARY KEY, btree (id)
"contact_address_address_l7_country_idx" btree (addressl7country)
"contact_address_commune_idx" btree (commune)
"contact_address_full_name_idx" btree (full_name)
Contraintes de clés étrangères :
"fk_4yx7nnewflhyjdm5tue5qntbg" FOREIGN KEY (commune) REFERENCES territory_commune(id)
"fk_5lwaygtve0ol8ma53picsdef" FOREIGN KEY (addressl7country) REFERENCES territory_country(id)
"fk_p9svu5ssynimpuu0is3j396lt" FOREIGN KEY (updated_by) REFERENCES auth_user(id)
"fk_rm0lcgnys2n97ad62jkm53qlt" FOREIGN KEY (created_by) REFERENCES auth_user(id)
Regards,
Laurent
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: IS NOT NULL and LEFT JOIN
@ 2014-10-19 08:41 David Rowley <dgrowleyml@gmail.com>
parent: Laurent Martelli <laurent.martelli@enercoop.org>
0 siblings, 1 reply; 9+ messages in thread
From: David Rowley @ 2014-10-19 08:41 UTC (permalink / raw)
To: Laurent Martelli <laurent.martelli@enercoop.org>; +Cc: pgsql-performance
On Sun, Oct 19, 2014 at 5:10 PM, Laurent Martelli <
laurent.martelli@enercoop.org> wrote:
> Hello there,
>
> I have a strange query plan involving an IS NOT NULL and a LEFT JOIN.
>
> I grant you that the query can be written without the JOIN on
> user_user_info,
> but it is generated like this by hibernate. Just changing the IS NOT NULL
> condition
> to the other side of useless JOIN makes a big difference in the query plan
> :
>
> -- THE BAD ONE : given the selectivity on c.name and c.email, barely more
> than one row will ever be returned
>
But it looks like you're ignoring the fact that the OR condition would
force the query to match not only the user and the email, but also any row
that finds a match in the user_user_info table, which going by the
planner's estimates, that's every row in the contract_contract table. This
is why the planner chooses a seqscan on the contract_contract table instead
of using the index on lower(name).
Is it really your intention to get all rows that find a this martelli
contract that has this email, and along with that, get every contract that
has a not null user_info record?
I see that you have a foreign key on c.user_info to reference the user, so
this should be matching everything with a non null user_info record.
explain analyze select c.*
> from contact_contact c
> left outer join user_user_info u on c.user_info=u.id
> left outer join contact_address a on c.address=a.id
> where lower(c.name)='martelli'
> and c.email='dsfze@ezrfz.com' or u.id is not null;
>
QUERY
> PLAN
>
> --------------------------------------------------------------------------------------------------------------------------------
> Hash Left Join (cost=1.83..2246.76 rows=59412 width=4012) (actual
> time=53.645..53.645 rows=0 loops=1)
> Hash Cond: (c.user_info = u.id)
> Filter: (((lower((c.name)::text) = 'martelli'::text) AND
> ((c.email)::text = 'dsfze@ezrfz.com'::text)) OR (u.id IS NOT NULL))
> Rows Removed by Filter: 58247
> -> Seq Scan on contact_contact c (cost=0.00..2022.12 rows=59412
> width=4012) (actual time=0.007..6.892 rows=58247 loops=1)
> -> Hash (cost=1.37..1.37 rows=37 width=8) (actual time=0.029..0.029
> rows=37 loops=1)
> Buckets: 1024 Batches: 1 Memory Usage: 2kB
> -> Seq Scan on user_user_info u (cost=0.00..1.37 rows=37
> width=8) (actual time=0.004..0.015 rows=37 loops=1)
> Planning time: 0.790 ms
> Execution time: 53.712 ms
>
> -- THE GOOD ONE (test IS NOT NULL on contact0_.user_info instead of
> userinfo1_.id)
> explain analyze select c.*
> from contact_contact c
> left outer join user_user_info u on c.user_info=u.id
> left outer join contact_address a on c.address=a.id
> where lower(c.name)='martelli'
> and c.email='dsfze@ezrfz.com' or c.user_info is not null;
> QUERY
> PLAN
>
> --------------------------------------------------------------------------------------------------------------------------------------------
> Bitmap Heap Scan on contact_contact c (cost=8.60..16.41 rows=1
> width=4012) (actual time=0.037..0.037 rows=0 loops=1)
> Recheck Cond: (((email)::text = 'dsfze@ezrfz.com'::text) OR (user_info
> IS NOT NULL))
> Filter: (((lower((name)::text) = 'martelli'::text) AND ((email)::text =
> 'dsfze@ezrfz.com'::text)) OR (user_info IS NOT NULL))
> -> BitmapOr (cost=8.60..8.60 rows=2 width=0) (actual
> time=0.034..0.034 rows=0 loops=1)
> -> Bitmap Index Scan on idx_contact_email (cost=0.00..4.30
> rows=2 width=0) (actual time=0.027..0.027 rows=0 loops=1)
> Index Cond: ((email)::text = 'dsfze@ezrfz.com'::text)
> -> Bitmap Index Scan on contact_contact_user_info_idx
> (cost=0.00..4.30 rows=1 width=0) (actual time=0.007..0.007 rows=0 loops=1)
> Index Cond: (user_info IS NOT NULL)
> Planning time: 0.602 ms
> Execution time: 0.118 ms
>
>
>
If you look closely at the 2nd query plan, you'll see that no joins are
performed, and it's only the contract_contract table that's looked at. This
is because PostgresSQL sees that none of the columns from the 2 tables
which are being left joined to are used, and also that the columns that
you're joining to on these tables are unique, therefore joining to them
cannot duplicate any rows, and since these are left joined, if there was no
matching row, then it wouldn't filter out rows from the contract_contract
table, as it would with INNER JOINs. The planner sees that these left joins
are pointless, so just removes them from the plan.
Regards
David Rowley
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: IS NOT NULL and LEFT JOIN
@ 2014-10-20 09:29 Laurent Martelli <laurent.martelli@enercoop.org>
parent: David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Laurent Martelli @ 2014-10-20 09:29 UTC (permalink / raw)
To: pgsql-performance
Hi David,
Do we agree that both queries are identical ? Since we join on
c.user_info=u.id <http://u.id; having u.id <http://u.id; is not null or
c.user_info is not null in the where clause is the same, isn't it ?
Since c.user_info=u.id <http://u.id; the condition onu.id is not null
does not use any *new* information from user_user_info.
Regards,
Laurent
Le 19/10/2014 10:41, David Rowley a écrit :
> On Sun, Oct 19, 2014 at 5:10 PM, Laurent Martelli
> <laurent.martelli@enercoop.org <mailto:laurent.martelli@enercoop.org>>
> wrote:
>
> Hello there,
>
> I have a strange query plan involving an IS NOT NULL and a LEFT JOIN.
>
> I grant you that the query can be written without the JOIN on
> user_user_info,
> but it is generated like this by hibernate. Just changing the IS
> NOT NULL condition
> to the other side of useless JOIN makes a big difference in the
> query plan :
>
> -- THE BAD ONE : given the selectivity on c.name <http://c.name;
> and c.email, barely more than one row will ever be returned
>
>
> But it looks like you're ignoring the fact that the OR condition would
> force the query to match not only the user and the email, but also any
> row that finds a match in the user_user_info table, which going by the
> planner's estimates, that's every row in the contract_contract table.
> This is why the planner chooses a seqscan on the contract_contract
> table instead of using the index on lower(name).
>
> Is it really your intention to get all rows that find a this martelli
> contract that has this email, and along with that, get every contract
> that has a not null user_info record?
>
> I see that you have a foreign key on c.user_info to reference the
> user, so this should be matching everything with a non null user_info
> record.
>
> explain analyze select c.*
> from contact_contact c
> left outer join user_user_info u on c.user_info=u.id
> <http://u.id;
> left outer join contact_address a on c.address=a.id
> <http://a.id;
> where lower(c.name <http://c.name>)='martelli';
> and c.email='dsfze@ezrfz.com <mailto:dsfze@ezrfz.com>' or
> u.id <http://u.id; is not null;
>
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------------
> Hash Left Join (cost=1.83..2246.76 rows=59412 width=4012)
> (actual time=53.645..53.645 rows=0 loops=1)
> Hash Cond: (c.user_info = u.id <http://u.id;)
> Filter: (((lower((c.name <http://c.name>)::text) =
> 'martelli'::text) AND ((c.email)::text = 'dsfze@ezrfz.com
> <mailto:dsfze@ezrfz.com>'::text)) OR (u.id <http://u.id; IS NOT NULL))
> Rows Removed by Filter: 58247
> -> Seq Scan on contact_contact c (cost=0.00..2022.12
> rows=59412 width=4012) (actual time=0.007..6.892 rows=58247 loops=1)
> -> Hash (cost=1.37..1.37 rows=37 width=8) (actual
> time=0.029..0.029 rows=37 loops=1)
> Buckets: 1024 Batches: 1 Memory Usage: 2kB
> -> Seq Scan on user_user_info u (cost=0.00..1.37 rows=37
> width=8) (actual time=0.004..0.015 rows=37 loops=1)
> Planning time: 0.790 ms
> Execution time: 53.712 ms
>
> -- THE GOOD ONE (test IS NOT NULL on contact0_.user_info instead
> of userinfo1_.id)
> explain analyze select c.*
> from contact_contact c
> left outer join user_user_info u on c.user_info=u.id
> <http://u.id;
> left outer join contact_address a on c.address=a.id
> <http://a.id;
> where lower(c.name <http://c.name>)='martelli';
> and c.email='dsfze@ezrfz.com <mailto:dsfze@ezrfz.com>' or
> c.user_info is not null;
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------------------------
> Bitmap Heap Scan on contact_contact c (cost=8.60..16.41 rows=1
> width=4012) (actual time=0.037..0.037 rows=0 loops=1)
> Recheck Cond: (((email)::text = 'dsfze@ezrfz.com
> <mailto:dsfze@ezrfz.com>'::text) OR (user_info IS NOT NULL))
> Filter: (((lower((name)::text) = 'martelli'::text) AND
> ((email)::text = 'dsfze@ezrfz.com
> <mailto:dsfze@ezrfz.com>'::text)) OR (user_info IS NOT NULL))
> -> BitmapOr (cost=8.60..8.60 rows=2 width=0) (actual
> time=0.034..0.034 rows=0 loops=1)
> -> Bitmap Index Scan on idx_contact_email
> (cost=0.00..4.30 rows=2 width=0) (actual time=0.027..0.027 rows=0
> loops=1)
> Index Cond: ((email)::text = 'dsfze@ezrfz.com
> <mailto:dsfze@ezrfz.com>'::text)
> -> Bitmap Index Scan on contact_contact_user_info_idx
> (cost=0.00..4.30 rows=1 width=0) (actual time=0.007..0.007 rows=0
> loops=1)
> Index Cond: (user_info IS NOT NULL)
> Planning time: 0.602 ms
> Execution time: 0.118 ms
>
>
> If you look closely at the 2nd query plan, you'll see that no joins
> are performed, and it's only the contract_contract table that's looked
> at. This is because PostgresSQL sees that none of the columns from the
> 2 tables which are being left joined to are used, and also that the
> columns that you're joining to on these tables are unique, therefore
> joining to them cannot duplicate any rows, and since these are left
> joined, if there was no matching row, then it wouldn't filter out rows
> from the contract_contract table, as it would with INNER JOINs. The
> planner sees that these left joins are pointless, so just removes them
> from the plan.
>
> Regards
>
> David Rowley
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: IS NOT NULL and LEFT JOIN
@ 2014-10-20 13:58 Tom Lane <tgl@sss.pgh.pa.us>
parent: Laurent Martelli <laurent.martelli@enercoop.org>
0 siblings, 2 replies; 9+ messages in thread
From: Tom Lane @ 2014-10-20 13:58 UTC (permalink / raw)
To: Laurent Martelli <laurent.martelli@enercoop.org>; +Cc: pgsql-performance
Laurent Martelli <laurent.martelli@enercoop.org> writes:
> Do we agree that both queries are identical ?
No, they *aren't* identical. Go consult any SQL reference. Left join
conditions don't work the way you seem to be thinking: after the join,
the RHS column might be null, rather than equal to the LHS column.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: IS NOT NULL and LEFT JOIN
@ 2014-10-20 21:02 Laurent Martelli <laurent.martelli@enercoop.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 9+ messages in thread
From: Laurent Martelli @ 2014-10-20 21:02 UTC (permalink / raw)
To: pgsql-performance
Le 20/10/2014 15:58, Tom Lane a écrit :
> Laurent Martelli <laurent.martelli@enercoop.org> writes:
>> Do we agree that both queries are identical ?
> No, they *aren't* identical. Go consult any SQL reference. Left join
> conditions don't work the way you seem to be thinking: after the join,
> the RHS column might be null, rather than equal to the LHS column.
Yes, I was wrong to assume that c.user_info=u.id because of the LEFT JOIN.
But since I only want rows where u.id IS NOT NULL, in any case I will
also have c.user_info IS NOT NULL.
Also, having a foreign key, if c.user_info is not null, it will have a
match in u. So in that case, either both c.user_info and c.id are null
in the result rows, or they are equal.
Regards,
Laurent
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: IS NOT NULL and LEFT JOIN
@ 2014-10-20 22:19 David G Johnston <david.g.johnston@gmail.com>
parent: Laurent Martelli <laurent.martelli@enercoop.org>
0 siblings, 1 reply; 9+ messages in thread
From: David G Johnston @ 2014-10-20 22:19 UTC (permalink / raw)
To: pgsql-performance
Laurent Martelli wrote
> Le 20/10/2014 15:58, Tom Lane a écrit :
>> Laurent Martelli <
> laurent.martelli@
> > writes:
>>> Do we agree that both queries are identical ?
>> No, they *aren't* identical. Go consult any SQL reference. Left join
>> conditions don't work the way you seem to be thinking: after the join,
>> the RHS column might be null, rather than equal to the LHS column.
> Yes, I was wrong to assume that c.user_info=u.id because of the LEFT JOIN.
>
> But since I only want rows where u.id IS NOT NULL, in any case I will
> also have c.user_info IS NOT NULL.
>
> Also, having a foreign key, if c.user_info is not null, it will have a
> match in u. So in that case, either both c.user_info and c.id are null
> in the result rows, or they are equal.
The planner only expends so much effort converting between equivalent query
forms. By adding u.id IS NOT NULL you are saying that you really meant to
use INNER JOIN instead of LEFT JOIN but whether the planner can and/or does
act on that information in the WHERE clause to modify its joins is beyond my
knowledge. It doesn't seem to and probably correctly isn't worth adding the
planner cycles to fix a poorly written/generated query on-the-fly.
Now that it has been pointed out that the two queries you supplied are
semantically different it is unclear what your point here is. It is known
that Hibernate (and humans too) will generate sub-optimal plans that can be
rewritten using relational algebra and better optimized for having done so.
But such work takes resources that would be expended for every single query
while manually rewriting the sub-optimal query solves the problem
once-and-for-all.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/IS-NOT-NULL-and-LEFT-JOIN-tp5823591p5823737.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: IS NOT NULL and LEFT JOIN
@ 2014-10-20 22:30 David G Johnston <david.g.johnston@gmail.com>
parent: David G Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: David G Johnston @ 2014-10-20 22:30 UTC (permalink / raw)
To: pgsql-performance
David G Johnston wrote
>
> Laurent Martelli wrote
>> Le 20/10/2014 15:58, Tom Lane a écrit :
>>> Laurent Martelli <
>> laurent.martelli@
>> > writes:
>>>> Do we agree that both queries are identical ?
>>> No, they *aren't* identical. Go consult any SQL reference. Left join
>>> conditions don't work the way you seem to be thinking: after the join,
>>> the RHS column might be null, rather than equal to the LHS column.
>> Yes, I was wrong to assume that c.user_info=u.id because of the LEFT
>> JOIN.
>>
>> But since I only want rows where u.id IS NOT NULL, in any case I will
>> also have c.user_info IS NOT NULL.
>>
>> Also, having a foreign key, if c.user_info is not null, it will have a
>> match in u. So in that case, either both c.user_info and c.id are null
>> in the result rows, or they are equal.
> The planner only expends so much effort converting between equivalent
> query forms. By adding u.id IS NOT NULL you are saying that you really
> meant to use INNER JOIN instead of LEFT JOIN but whether the planner can
> and/or does act on that information in the WHERE clause to modify its
> joins is beyond my knowledge. It doesn't seem to and probably correctly
> isn't worth adding the planner cycles to fix a poorly written/generated
> query on-the-fly.
>
>
> Now that it has been pointed out that the two queries you supplied are
> semantically different it is unclear what your point here is. It is known
> that Hibernate (and humans too) will generate sub-optimal plans that can
> be rewritten using relational algebra and better optimized for having done
> so. But such work takes resources that would be expended for every single
> query while manually rewriting the sub-optimal query solves the problem
> once-and-for-all.
>
> David J.
Didn't sound right what I wrote above...
The presence of the "OR" screws things up even further since it does force
the use of LEFT JOIN mechanics for the single case where the name and e-mail
match.
I would maybe try a UNION DISTINCT query instead of an OR clause if you want
to have a query that performs better than the Hibernate one...otherwise
others more knowledgeable than myself have not made any indication that the
planner is unintentionally deficient in its handling of your original query.
You may try posting your actual question, and not the SQL, and see if that
sparks any suggestions.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/IS-NOT-NULL-and-LEFT-JOIN-tp5823591p5823739.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: IS NOT NULL and LEFT JOIN
@ 2014-10-21 08:44 David Rowley <dgrowleyml@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 9+ messages in thread
From: David Rowley @ 2014-10-21 08:44 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Laurent Martelli <laurent.martelli@enercoop.org>; pgsql-performance
On Tue, Oct 21, 2014 at 2:58 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Laurent Martelli <laurent.martelli@enercoop.org> writes:
> > Do we agree that both queries are identical ?
>
> No, they *aren't* identical. Go consult any SQL reference. Left join
> conditions don't work the way you seem to be thinking: after the join,
> the RHS column might be null, rather than equal to the LHS column.
>
>
>
For what it's worth I'd say they are identical, at least, if you discount
deferring foreign key constraints or also executing the query from within
a volatile function which was called by a query which just updated the
user_info table to break referential integrity.
The presence of the foreign key on contract_contract.user_info which
references user_user_info.id means that any non-null
contract_contract.user_info record must reference a valid user_user_info
record, therefore the join is not required to prove that a non nulled
user_info contract records match a user info record, therefore the join to
check it exists is pretty much pointless in just about all cases that
you're likely to care about.
Although, saying that I'm still a bit confused about the question. Are you
asking if there's some way to get PostgreSQL to run the 1st query faster?
Or are you asking if both queries are equivalent?
Regards
David Rowley
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: IS NOT NULL and LEFT JOIN
@ 2014-10-22 06:29 Laurent Martelli <laurent.martelli@enercoop.org>
parent: David Rowley <dgrowleyml@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Laurent Martelli @ 2014-10-22 06:29 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Le Mardi 21 Octobre 2014 10:44 CEST, David Rowley <dgrowleyml@gmail.com> a écrit:
> For what it's worth I'd say they are identical, at least, if you discount
> deferring foreign key constraints or also executing the query from within
> a volatile function which was called by a query which just updated the
> user_info table to break referential integrity.
I must say I had not thought of that.
> The presence of the foreign key on contract_contract.user_info which
> references user_user_info.id means that any non-null
> contract_contract.user_info record must reference a valid user_user_info
> record, therefore the join is not required to prove that a non nulled
> user_info contract records match a user info record, therefore the join to
> check it exists is pretty much pointless in just about all cases that
> you're likely to care about.
>
> Although, saying that I'm still a bit confused about the question. Are you
> asking if there's some way to get PostgreSQL to run the 1st query faster?
> Or are you asking if both queries are equivalent?
I was asking for a way to make it run faster. Given that it returns at most a few rows found by an index, I was thinking it could be made to run faster.
But I agree that the query is not well written (well generated by hibernate) considering the result I want.
Regards,
Laurent
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2014-10-22 06:29 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-10-19 04:10 IS NOT NULL and LEFT JOIN Laurent Martelli <laurent.martelli@enercoop.org>
2014-10-19 08:41 ` David Rowley <dgrowleyml@gmail.com>
2014-10-20 09:29 ` Laurent Martelli <laurent.martelli@enercoop.org>
2014-10-20 13:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-10-20 21:02 ` Laurent Martelli <laurent.martelli@enercoop.org>
2014-10-20 22:19 ` David G Johnston <david.g.johnston@gmail.com>
2014-10-20 22:30 ` David G Johnston <david.g.johnston@gmail.com>
2014-10-21 08:44 ` David Rowley <dgrowleyml@gmail.com>
2014-10-22 06:29 ` Laurent Martelli <laurent.martelli@enercoop.org>
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