Re: JOIN issues (Left vs Right for sorting), and "Nested Loop" problem

On Sep 1, 2007, at 14:48, Phoenix Kiula wrote:

> On 01/09/07, Alban Hertroys <alban(at)magproductions(dot)nl> wrote:
>>
>> On Sep 1, 2007, at 11:46, Phoenix Kiula wrote:
> .
> ..snip....
>
>>> However, there's a nested loop in there as the EXPLAIN ANALYZE shows
>>> below. What is causing this nested loop?
>>
>> It looks like it's used to match trades to tradecounts. I think that
>> makes sense, as the number of matching records from both tables isn't
>> necessarily equal. The query is looping over trades until each
>> tradecount has all its trades (for user 'jondoe' with status 'Y')
>> associated.
>
>
> So are you suggesting that it would help performance if the number of
> rows in each table were to be exactly the same? It can be done I
> suppose, but according to our business logic at the moment, the counts
> table gets a corresponding row when there is at least one count.
> Otherwise, there is nothing for an "id" in the tradecount table, so
> "u_count" comes back to us as null.

No, it wouldn't help I think. The query planner still would have no
way of being sure of that, it doesn't know about your business logic.
I'm not entirely sure that's the problem even...

Is that combination of user_id with a specific status something
you'll be querying a lot? In that case it may help to create an index
over that combination, or a partial index on user_id where status =
'Y' holds true.

I am kind of surprised that the planner doesn't understand that a
foreign key with a unique constraint (which a primary key is) means
there is a 0..1 to 1 relationship with the target table.

>> It is kind of confusing that you're using the id column in
>> tradecounts for both primary key and foreign key, and I'm not sure
>> what that implies to the query planner. It suggests that there can be
>> only (up to) one tradecounts record for each trade count, but it
>> appears that either the planner doesn't realise that...
>
>
> If I drop the primary key and leave only the foreign key, will this
> column still be indexed (sorry if this is a stupid question). I can
> drop primary if that is true, but I do want to leave the foreign key
> intact because of the "ON DELETE CASCADE" feature to maintain data
> integrity.

The index wouldn't drop with the dropping of the constraint. It also
has no relevance to the ON DELETE CASCADE; that's part of the foreign
key constraint and unrelated to other indices on that table.

Having an index on that column would help though, and if it's
required to be unique I'd probably opt for a unique constraint on it
(which creates a unique index for you). PostgreSQL doesn't
automatically create indices on foreign keys, btw.

In fact there's nothing wrong with your combined primary/foreign key,
except that I think it _might_ confuse the planner. I am not
knowledgeable enough to say for sure.

>> Is 10 ms problematic for this query?
>
>
> I think you got 10ms from the query plan? These queries are very fast
> after they have been executed once. But the first time is huge.
> Sometimes I have to wait as much as 10 seconds (10,000ms?)

10s for a join of what... 2 times 386 rows? That can't be right.
Sequential scans would be faster than that (by much). Are you running
out of memory for that query maybe? Or are you looking at a DNS time
out? Something is wrong there.

>> You could use ORDER BY COALESCE(tradecount.u_count, 0) desc if you
>> want it to behave like you say mysql sorted it.
>
> Yes, this does it! I didn't think about the NULL stuff, and yes MySQL
> returns NULLs in integer columns as a 0, so those queries work. I

It does? Oh dear... Then how do they expect you to see that there was
an actual 0 in that column instead of a NULL?

> guess I could use the IFNULL or something, but thanks for the COALESCE
> idea, this is great. It works. I just hope sorting by a function does
> not cause a major hit to query performance, so I'll be watching over
> the next few days.

Not much, AFAIK. But in the worst case you could create a functional
index on that column. That'd move the calculation into the creation
of the index and would only add a small penalty on inserting and
updating.

--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //

!DSPAM:737,46d97e1e289908046410233!