Re: Help Me Understand Why I'm Getting a Bad Query Plan

On Tue, Mar 24, 2009 at 10:04 PM, marcin mank <marcin(dot)mank(at)gmail(dot)com> wrote:
> There is one thing I don`t understand:
>
>              ->  Nested Loop  (cost=0.00..180564.28 rows=1806
> width=37) (actual time=0.192..60.214 rows=3174 loops=1)
>                    ->  Index Scan using visitors_userid_index2 on
> visitors v  (cost=0.00..2580.97 rows=1300 width=33) (actual
> time=0.052..2.342 rows=897 loops=1)
>                          Index Cond: (userid =
> 'fbe2537f21d94f519605612c0bf7c2c5'::bpchar)
>                    ->  Index Scan using
> itemexperiencelog__index__visitorid on itemexperiencelog l
> (cost=0.00..134.04 rows=230 width=70) (actual time=0.013..0.040 rows=4
> loops=897)
>                          Index Cond: (l.visitorid = v.id)
>
> If it expects 1300 visitors with the userid, and for each of them to
> have 230 entries in itemexperiencelog, how can it come up with 1806
> returned rows (and be about right!)?

I'm not sure I follow what you're saying.

One thing to keep in mind, due to a lapse in our judgement at the
time, this itemexperiencelog table serves as both a current state
table, and a log table. Therefore, it potentially has multiple
redundant entries, but we typically only look at the most recent entry
to figure out the state of the current item.

We're in the process of re-factoring this now, as well as
denormalizing some of the tables to eliminate unnecessary joins, but I
keep running into these problems and need to understand what is going
on so that I know we're fixing the correct things.

Thanks,
Bryan