| From: | Christoph Frick <frick(at)sc-networks(dot)com> |
|---|---|
| To: | pgsql-novice(at)postgresql(dot)org |
| Subject: | 7.4.7: due to optimizing of my query logik breaks |
| Date: | 2005-09-14 09:21:16 |
| Message-ID: | 20050914092116.GZ22404@byleth.sc-networks.de |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | 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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2005-09-14 14:03:37 | Re: 7.4.7: due to optimizing of my query logik breaks |
| Previous Message | Michael Swierczek | 2005-09-13 20:27:54 | question regarding contrib/tablefunc |