Thread: PGSQL and Nested Loops

hi all,  i read the followin gon slashdot...

(*) Nested loops are like candy to SQL server, and
I've heard this is the same for Sybase
(understandably). Deep sets of nested loops will kick
the other databases I've tested in the teeth. Given an
instruction with several nested loops and 16 million
rows of data, I got results from SQL server in 5
minutes, results from Oracle 9 in an hour, and results
from Postgres in 18 hours. This was a year ago and
Postgres has changed, so it might be better now. Does
MySQL handle them well?

i don't see myself ever running into this kind of
traffic, but i'm curious to know more about my db of
choice - pgsql.  is this person's assessment correct
(for a year old version of pgsql) and has it improved
since then?

tia...




______________________________________________________
Click here to donate to the Hurricane Katrina relief effort.
http://store.yahoo.com/redcross-donate3/

--- operationsengineer1@yahoo.com wrote:

> hi all,  i read the followin gon slashdot...
>
> (*) Nested loops are like candy to SQL server, and
> I've heard this is the same for Sybase
> (understandably). Deep sets of nested loops will
> kick
> the other databases I've tested in the teeth. Given
> an
> instruction with several nested loops and 16 million
> rows of data, I got results from SQL server in 5
> minutes, results from Oracle 9 in an hour, and
> results
> from Postgres in 18 hours. This was a year ago and
> Postgres has changed, so it might be better now.
> Does
> MySQL handle them well?
>
> i don't see myself ever running into this kind of
> traffic, but i'm curious to know more about my db of
> choice - pgsql.  is this person's assessment correct
> (for a year old version of pgsql) and has it
> improved
> since then?
>
> tia...

the author of the above at slashdot wrote this...

--
I'm a cut-n-paste SQL writer, so I may go off into
left field here quickly...

The condition I was referring to, which was called
nested looping by the DBAs and development lead at the
company I worked at, is the condition where one query
causes additional queries to be performed. Think
queries against dynamic view tables. In this case, the
nightmare query went four or five levels deep. I
recently saw an Oracle trace in which a data view was
unrolling to 88 levels deep...

Keep in mind this was over a year ago and it probably
doesn't represent best coding practice. However, the
most popular supported platform performed just fine
with it, which tends to make a company loathe to spend
much time finding workarounds for the other platforms.
--

since he's a cut and paste sql writer, i may have
jumped the gun since he may be doing everying wrong
and then complaining pgsql isn't as efficient as a
wrong to do things.  ;-)

anyway, *if* his point is valid, i'd appreciate input.

thanks and i hope everyone is having a great holiday.




______________________________________________________
Click here to donate to the Hurricane Katrina relief effort.
http://store.yahoo.com/redcross-donate3/

<operationsengineer1@yahoo.com> writes:
> since he's a cut and paste sql writer, i may have
> jumped the gun since he may be doing everying wrong
> and then complaining pgsql isn't as efficient as a
> wrong to do things.  ;-)

> anyway, *if* his point is valid, i'd appreciate input.

With no specifics as to what he was doing, it's impossible to evaluate
whether the complaint is valid or not.  I don't say he lied about the
times he observed, but there's no way to tell if it's a stupid query
design, failure to configure/tune the database properly, or a genuine
Postgres performance issue --- much less whether any such issue has
been fixed later.

I would translate the complaint as follows: "I know about SQL Server
and I can't be bothered to learn about Postgres".  Pretty much a
self-fulfilling prophecy, eh?

            regards, tom lane