[BUGS] BUG #14672: with UTF-8,indexes are not used with order by on multiple tables - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      14672
Logged by:          Eric Quinton
Email address:      eric.quinton@irstea.fr
PostgreSQL version: 9.5.6
Operating system:   Ubuntu
Description:

When an order clause use more than one table, with utf-8 locale, indexes are
not used.
The problem is not found with C locale.
Request : 

explain sELECT s.sample_id, s.uid, s.project_id, project_name,
s.sample_type_id, s.dbuid_origin, sample_type_name, s.sample_creation_date,
s.sample_date, s.parent_sample_id, st.multiple_type_id, s.multiple_value,
st.multiple_unit, mt.multiple_type_name, so.identifier, so.wgs84_x,
so.wgs84_y, so.object_status_id, object_status_name, pso.uid AS parent_uid,
pso.identifier AS parent_identifier, container_type_name,
clp_classification, operation_id, protocol_name, protocol_year,
protocol_version, operation_name, operation_order, document_id, identifiers,
storage_date, movement_type_name, movement_type_id, sp.sampling_place_id,
sp.sampling_place_name FROM sample s JOIN sample_type st ON
(st.sample_type_id = s.sample_type_id) JOIN project p ON (p.project_id =
s.project_id) JOIN object so ON (s.uid = so.uid) LEFT OUTER JOIN
sampling_place sp ON (sp.sampling_place_id = s.sampling_place_id) LEFT OUTER
JOIN object_status os ON (so.object_status_id = os.object_status_id) LEFT
OUTER JOIN sample ps ON (s.parent_sample_id = ps.sample_id) LEFT OUTER JOIN
object pso ON (ps.uid = pso.uid) LEFT OUTER JOIN container_type ct USING
(container_type_id) LEFT OUTER JOIN operation USING (operation_id) LEFT
OUTER JOIN protocol USING (protocol_id) LEFT OUTER JOIN multiple_type mt ON
(st.multiple_type_id = mt.multiple_type_id) LEFT OUTER JOIN last_photo ON
(so.uid = last_photo.uid) LEFT OUTER JOIN v_object_identifier voi ON (s.uid
= voi.uid) LEFT OUTER JOIN last_movement lm ON (s.uid = lm.uid) LEFT OUTER
JOIN movement_type USING (movement_type_id) WHERE (UPPER(so.identifier) LIKE
upper('%avril-2017%') OR UPPER(s.dbuid_origin) = UPPER('avril-2017') OR
UPPER(identifiers) LIKE 'avril-2017') AND ( so.object_status_id = 1 and
st.sample_type_id = 1) ORDER BY project_name, sample_type_name, identifier,
so.uid LIMIT 100 ;

result with order by:
QUERY PLAN Limit (cost=225715.42..225715.54 rows=48 width=494) -> Sort
(cost=225715.42..225715.54 rows=48 width=494) Sort Key: p.project_name,
st.sample_type_name, so.identifier, s.uid -> Nested Loop Left Join
(cost=3.09..225714.08 rows=48 width=494) Join Filter: (s.uid = s_1.uid) ->
Nested Loop Left Join (cost=3.09..9223.69 rows=48 width=469) Join Filter:
(so.uid = d.uid) -> Nested Loop Left Join (cost=3.09..1895.80 rows=48
width=465) Join Filter: (st.multiple_type_id = mt.multiple_type_id) ->
Nested Loop Left Join (cost=3.09..1891.87 rows=48 width=454) Join Filter:
(operation.protocol_id = protocol.protocol_id) -> Nested Loop Left Join
(cost=3.09..1427.72 rows=48 width=392) Join Filter: (st.operation_id =
operation.operation_id) -> Nested Loop Left Join (cost=3.09..589.99 rows=48
width=352) Join Filter: (st.container_type_id = ct.container_type_id) ->
Nested Loop Left Join (cost=3.09..568.48 rows=48 width=306) Join Filter:
(s.uid = voi.uid) Filter: ((upper((so.identifier)::text) ~~
'%AVRIL-2017%'::text) OR (upper((s.dbuid_origin)::text) =
'AVRIL-2017'::text) OR (upper(voi.identifiers) ~~ 'avril-2017'::text)) ->
Nested Loop Left Join (cost=0.99..553.30 rows=144 width=274) -> Nested Loop
Left Join (cost=0.71..501.99 rows=144 width=257) -> Nested Loop Left Join
(cost=0.43..325.71 rows=144 width=253) Join Filter: (so.object_status_id =
os.object_status_id) -> Nested Loop Left Join (cost=0.43..322.47 rows=144
width=230) -> Nested Loop (cost=0.28..252.55 rows=144 width=198) -> Nested
Loop (cost=0.00..75.91 rows=144 width=157) Join Filter: (s.project_id =
p.project_id) -> Nested Loop (cost=0.00..59.70 rows=144 width=144) -> Seq
Scan on sample s (cost=0.00..56.77 rows=144 width=80) Filter:
(sample_type_id = 1) -> Materialize (cost=0.00..1.13 rows=1 width=68) -> Seq
Scan on sample_type st (cost=0.00..1.12 rows=1 width=68) Filter:
(sample_type_id = 1) -> Materialize (cost=0.00..1.10 rows=7 width=17) -> Seq
Scan on project p (cost=0.00..1.07 rows=7 width=17) -> Index Scan using
object_pk on object so (cost=0.28..1.22 rows=1 width=41) Index Cond: (uid =
s.uid) Filter: (object_status_id = 1) -> Index Scan using sampling_place_pk
on sampling_place sp (cost=0.15..0.48 rows=1 width=36) Index Cond:
(sampling_place_id = s.sampling_place_id) -> Materialize (cost=0.00..1.08
rows=1 width=27) -> Seq Scan on object_status os (cost=0.00..1.07 rows=1
width=27) Filter: (object_status_id = 1) -> Index Scan using sample_pk on
sample ps (cost=0.28..1.21 rows=1 width=8) Index Cond: (s.parent_sample_id =
sample_id) -> Index Scan using object_pk on object pso (cost=0.28..0.35
rows=1 width=21) Index Cond: (ps.uid = uid) -> Materialize (cost=2.10..2.23
rows=3 width=36) -> Subquery Scan on voi (cost=2.10..2.21 rows=3 width=36)
-> GroupAggregate (cost=2.10..2.18 rows=3 width=14) Group Key:
object_identifier.uid -> Sort (cost=2.10..2.11 rows=3 width=14) Sort Key:
object_identifier.uid -> Nested Loop (cost=0.00..2.08 rows=3 width=14) Join
Filter: (object_identifier.identifier_type_id =
identifier_type.identifier_type_id) -> Seq Scan on identifier_type
(cost=0.00..1.01 rows=1 width=8) -> Seq Scan on object_identifier
(cost=0.00..1.03 rows=3 width=14) -> Materialize (cost=0.00..1.42 rows=28
width=54) -> Seq Scan on container_type ct (cost=0.00..1.28 rows=28
width=54) -> Materialize (cost=0.00..26.95 rows=1130 width=44) -> Seq Scan
on operation (cost=0.00..21.30 rows=1130 width=44) -> Materialize
(cost=0.00..19.30 rows=620 width=70) -> Seq Scan on protocol
(cost=0.00..16.20 rows=620 width=70) -> Materialize (cost=0.00..1.06 rows=4
width=15) -> Seq Scan on multiple_type mt (cost=0.00..1.04 rows=4 width=15)
-> Materialize (cost=0.00..7326.46 rows=2 width=8) -> Seq Scan on document d
(cost=0.00..7326.45 rows=2 width=8) Filter: (document_id = (SubPlan 1))
SubPlan 1 -> Limit (cost=17.00..17.00 rows=1 width=20) -> Sort
(cost=17.00..17.00 rows=1 width=20) Sort Key: d1.document_creation_date
DESC, d1.document_import_date DESC, d1.document_id DESC -> Seq Scan on
document d1 (cost=0.00..16.99 rows=1 width=20) Filter: ((d.uid = uid) AND
(mime_type_id = ANY ('{4,5,6}'::integer[]))) -> Materialize
(cost=0.00..216478.91 rows=16 width=29) -> Nested Loop Left Join
(cost=0.00..216478.83 rows=16 width=29) Join Filter: (s_1.movement_type_id =
movement_type.movement_type_id) -> Seq Scan on storage s_1
(cost=0.00..216477.33 rows=16 width=20) Filter: (storage_id = (SubPlan 2))
SubPlan 2 -> Limit (cost=69.14..69.14 rows=1 width=12) -> Sort
(cost=69.14..69.14 rows=1 width=12) Sort Key: st_1.storage_date DESC -> Seq
Scan on storage st_1 (cost=0.00..69.12 rows=1 width=12) Filter: (s_1.uid =
uid) -> Materialize (cost=0.00..1.03 rows=2 width=17) -> Seq Scan on
movement_type (cost=0.00..1.02 rows=2 width=17)

without order by (first lines uniquely):
QUERY PLAN Nested Loop Left Join (cost=197.44..224159.61 rows=48 width=490)
Join Filter: (s.uid = s_1.uid) -> Nested Loop Left Join
(cost=197.44..7669.22 rows=48 width=465) Join Filter: (so.uid = d.uid) ->
Hash Left Join (cost=197.44..341.33 rows=48 width=465)


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs