Thread: Strange select query
Hellow,
I notice wired behavior of backend,
for instance I've 3 tables TA, TB, TC with about 1k of records each,
and I do something like that:
select * from TA as a, TB as b, TC as c where
position('some text' in a.textfield)>0 or
(position('some text' in b.textfield)>0 and a.index=b.referencefield)
or
(position('some text' in c.textfield)>0 and a.index=b.referencefield
and b.other_referencefield=c.index);
the backend start to execute query, but it seems to be 'never
ending story' because after 15 minutes of work I still haven't result,
and backend still was eating my RAM. I know that this query is
ugly and bad, but I think backend will reject such query at parsing.
regards
Robert Partyka
On Wed, Sep 12, 2001 at 10:28:33AM +0200, Partyka Robert wrote:
> Hellow,
>
> I notice wired behavior of backend,
>
> for instance I've 3 tables TA, TB, TC with about 1k of records each,
>
> and I do something like that:
>
> select * from TA as a, TB as b, TC as c where
> position('some text' in a.textfield)>0 or
> (position('some text' in b.textfield)>0 and a.index=b.referencefield)
> or
> (position('some text' in c.textfield)>0 and a.index=b.referencefield
> and b.other_referencefield=c.index);
Are you sure that is what you want?
This should result in
(TB x TC x rows in TA matching clause 1) + (TC x rows in TA matching clause 2) + (rows in TA matching clause 3)
rows being returned. If 10 rows match clause 1 and 3 rows clause 2 and 1 row clause 3 that will make
(assuming all tables have 1000 records)
r = a + b + c
a = 1000 * 1000 * 10 = 10 000 000
b = 1000 * 3 = 3 000
c = 1 = 1
r = 10 003 001 rows being returned.
> the backend start to execute query, but it seems to be 'never
> ending story' because after 15 minutes of work I still haven't result,
> and backend still was eating my RAM. I know that this query is
> ugly and bad, but I think backend will reject such query at parsing.
>
The query is valid you should get more ram or rethink your query :-)
- Einar Karttunen
Hello
> The query is valid you should get more ram or rethink your query :-)
thx for explain how meny records it will return,
I know that was my mistake because the correct query I need was
something like:
select * from TA as a, TB as b, TC as c where
(position('some text' in a.textfield)>0 and a.index=b.referencefield
and b.other_referencefield=c.index) or
(position('some text' in b.textfield)>0 and a.index=b.referencefield
and b.other_referencefield=c.index)
or
(position('some text' in c.textfield)>0 and a.index=b.referencefield
and b.other_referencefield=c.index);
I was just wonder if that was bad query and take never ending loop
in backend or just return so many records.
In fact i should get this reflection self ... so maybe it was brain lock
that I dont saw this explanation (sig 'take some rest' from brain? ;-)
). I was suggested by result show by psql (I saw one the same
record showed about 100 times before I ^C the query so i think 'oh.
its probably never ending loop.').
regards
Robert Partyka
I wonder if that wouldn't be better written as:
select * from TA as a, TB as b, TC as c where
a.index=b.referencefield and b.other_referencefield=c.index and (
(position('some text' in a.textfield)>0) or
(position('some text' in b.textfield)>0) or
(position('some text' in c.textfield)>0)
);
so that you only check your link fields once. Further, if you just want to see
whether a.textfield contains 'some text', you don't have to do it Visual Basic
style. Postgresql supplies the ~ and LIKE operators for just that situation:
select * from TA as a, TB as b, TC as c where
a.index=b.referencefield and b.other_referencefield=c.index and (
a.textfield LIKE '%some text%' or
b.textfield LIKE '%some text%' or
c.textfield LIKE '%some text%'
)
Check the docs for LIKE, ILIKE, ~~ and ~ operators for details.
http://www.postgresql.org/idocs/index.php?functions-matching.html.
HTH.
Wes Sheldahl
"Partyka Robert" <bobson%saturn.alpha.pl@interlock.lexmark.com> on 09/12/2001
05:16:05 AM
To: Einar Karttunen <ekarttun%cs.helsinki.fi@interlock.lexmark.com>
cc: pgsql-general%postgresql.org@interlock.lexmark.com (bcc: Wesley
Sheldahl/Lex/Lexmark)
Subject: Re: [GENERAL] Strange select query
Hello
> The query is valid you should get more ram or rethink your query :-)
thx for explain how meny records it will return,
I know that was my mistake because the correct query I need was
something like:
select * from TA as a, TB as b, TC as c where
(position('some text' in a.textfield)>0 and a.index=b.referencefield
and b.other_referencefield=c.index) or
(position('some text' in b.textfield)>0 and a.index=b.referencefield
and b.other_referencefield=c.index)
or
(position('some text' in c.textfield)>0 and a.index=b.referencefield
and b.other_referencefield=c.index);
I was just wonder if that was bad query and take never ending loop
in backend or just return so many records.
In fact i should get this reflection self ... so maybe it was brain lock
that I dont saw this explanation (sig 'take some rest' from brain? ;-)
). I was suggested by result show by psql (I saw one the same
record showed about 100 times before I ^C the query so i think 'oh.
its probably never ending loop.').
regards
Robert Partyka
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly