IS NOT NULL and LEFT JOIN - Mailing list pgsql-performance
| From | Laurent Martelli |
|---|---|
| Subject | IS NOT NULL and LEFT JOIN |
| Date | |
| Msg-id | 544339CA.5090202@enercoop.org Whole thread Raw |
| Responses |
Re: IS NOT NULL and LEFT JOIN
(David Rowley <dgrowleyml@gmail.com>)
|
| List | 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
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
pgsql-performance by date: