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

On 01/09/07, Alban Hertroys <alban@magproductions.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.


> 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.



> 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?)



> > QUERY PLAN
> > ----------------------------------------------------------------------
> > --------------------------------------------------
> >  Limit  (cost=4829.70..4829.73 rows=10 width=125) (actual
> > time=9.784..9.835 rows=10 loops=1)
> >    ->  Sort  (cost=4829.65..4830.61 rows=385 width=125) (actual
> > time=9.703..9.757 rows=30 loops=1)
> >          Sort Key: tradecount.u_count
> >          ->  Nested Loop Left Join  (cost=0.00..4813.12 rows=385
> > width=125) (actual time=0.075..8.662 rows=386 loops=1)
> >                ->  Index Scan using idx_trades_userid on trades
> > (cost=0.00..1556.08 rows=385 width=117) (actual time=0.05
> > 0..1.225 rows=386 loops=1)
> >                      Index Cond: ((user_id)::text = 'jondoe'::text)
> >                      Filter: (status = 'Y'::bpchar)
> >                ->  Index Scan using tradecount_pkey on tradecount
> > (cost=0.00..8.45 rows=1 width=16) (actual time=0.006.
> > .0.008 rows=1 loops=386)
> >                      Index Cond: (trades.id = tradecount.id)
> >  Total runtime: 9.963 ms
> > (10 rows)
> >
> >
> >
> >
> > 2. Secondly, if I want to sort the join by a column on the second
> > table, then the rows returned are not really sorted unless I do a
> > RIGHT JOIN (my sql above shows a LEFT JOIN). Getting results from a
> > right join is fine as long as the column is not null in the second
> > table, but if it is null, then nothing is returned. This is why I do a
> > LEFT join in the first place! So my question: how can I do a left
> > join, which is the logic that I wish to accomplish, but get the
> > sorting to work from the second table and if a column is null then
> > just return as 0 instead of nothing at all? (The LEFT JOIN used to
> > work in Mysql).

 > 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
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.


TIA!