>> I expected that making planner to use primary key index in case of >> >> WHERE bar::CHAR(3)='ABC' >> WHERE bar BETWEEN 'ABC' AND 'ABC' || CHR(255) >> WHERE SUBSTRING( bar FOR 3 )='ABC' > > Which doesn't necessarily work because of how some collations may work. > The first 3 characters of the string may sort to a fundamentally different > place than the first 4 characters. You can have a collation where > 'ABC' < 'ABCR' < 'ABCT' < 'ABD' < 'ABZ' < 'ABCS'. In the above, the ABCS > value shortens so it should be found, however it's sort position in the > index is outside the normal ABC range. Thank you. I missed this since this does not occur in my locale. Now I understood that cryptics text_pattern_ops, varchar_pattern_ops, bpchar_pattern_ops actually mean "binary_sort_order". > AFAIK the interfaces being used > don't give enough information to detect such locales to handle them > differently. Maybe there is some setting which I can use to inform Postgres that sorting order is strictly by single byte, not affected by byte sequences. Maybe this information can be get from Windows API or from iconv lc_collate='C' assumes also binary order which is to restrictive. lc_collate shoud be divided into two: lc_collate_order and lc_collate_multibyte . lc_collate_multibyte has values true and false. In this case primary key for LIKE comparison can be used in some non-C locales. My desicion based on this thread for my coding is: 1. Write all partial match queries using LIKE operator like foo LIKE 'ABC%' 2. When data access becomes slow, create duplicate primary key index using text_pattern_ops or change only lc_collate to C by retaining all other settings non-C locale. I'm afraid that my users use % and ? characters in data. Using LIKE requires escaping % and ? characters in search patterns. It should be nicer to use foo:CHAR(3)='ABC' type expressions which do not require ? and % escaping. I understand that Postgres cannot use any index to speed up searches like foo:CHAR(3)='ABC' which does not use regular expression match operators. So LIKE with escaping is the only way. Andrus. ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
