Skip site navigation (1) Skip section navigation (2)

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

From: Bryan Murphy <bmurphy1976(at)gmail(dot)com>
To: marcin mank <marcin(dot)mank(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Help Me Understand Why I'm Getting a Bad Query Plan
Date: 2009-03-25 03:47:00
Message-ID: 7fd310d10903242047t1a7b7daw1d61c3de7f20b7c6@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Greg SmithDate: 2009-03-25 08:07:50
Subject: Re: Raid 10 chunksize
Previous:From: Bryan MurphyDate: 2009-03-25 03:43:37
Subject: Re: Help Me Understand Why I'm Getting a Bad Query Plan

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group