7.4.7: due to optimizing of my query logik breaks
От | Christoph Frick |
---|---|
Тема | 7.4.7: due to optimizing of my query logik breaks |
Дата | |
Msg-id | 20050914092116.GZ22404@byleth.sc-networks.de обсуждение исходный текст |
Ответы |
Re: 7.4.7: due to optimizing of my query logik breaks
|
Список | pgsql-novice |
hi, i have a zip code field in a table that is used by "international" customers, which lead to very "random" data there. but a query should be possible "by number" if there is only a number in the field. for queriing the table an admin user can generate queries with a visual interface - but the optimizier then kills my logic: [fragment from the query - plz is the zip-code field] ... ) AND ( eintrag.email like '%example.com' -- #1 OR ( eintrag.plz ~ '^[0-9]{1,9}$' AND int4(eintrag.plz) = '0' ) -- #2 ) ... an EXPLAIN shows me the result: ... ) AND ( ( (plz)::text ~ '^[0-9]{1,9}$'::text ) OR ( email ~~ '%example.com'::text ) ) AND ( ( int4((plz)::text) = 0 ) OR ( email ~~ '%example.com'::text ) ) ... of course this somehow "correct" assuming only boolean-logic - but not for someone thinking in terms of a C-programmer. please note: i can not reorder the #1 and #2 query parts, as the user gives the order. i can only influence the generated sql-code to some extends. of course another option would be to "fix the cast, if its numbers" - but i have lots of other query parts, that contain more then one compare itself that depend on each other. -- cu
Вложения
В списке pgsql-novice по дате отправления: