7.4.7: due to optimizing of my query logik breaks

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

Responses

Browse pgsql-novice by date

  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