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: | Raw Message | Whole Thread | 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 |