pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Bryan Murphy <bmurphy1976@gmail.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Help Me Understand Why I'm Getting a Bad Query Plan
Date: Wed, 25 Mar 2009 17:14:45 -0500
Message-ID: <7fd310d10903251514x1dd522f3o8d13a9218a6eda6c@mail.gmail.com> (raw)
In-Reply-To: <49CAA85C.8080402@agliodbs.com>
References: <7fd310d10903231203r1bd37b8cn8baeadae83253f44@mail.gmail.com>
	<7fd310d10903241521v1cbf5d7aueca653e03ed98649@mail.gmail.com>
	<b1b9fac60903242004p5fe8b68fwd6f1ada767766b21@mail.gmail.com>
	<7fd310d10903242047t1a7b7daw1d61c3de7f20b7c6@mail.gmail.com>
	<49CAA85C.8080402@agliodbs.com>

On Wed, Mar 25, 2009 at 4:55 PM, Josh Berkus <josh@agliodbs.com> wrote:
> Oh, I see.  It thinks that it'll need to pull 260,000 redundant rows in
> order to get 1800 unique ones.  Only it's wrong; you're only pulling about
> 4000.
>
> Try increasing some stats still further: itemexperiencelog.visitorid and
> visitors.user_id both to 500.

I tried that already, but I decided to try again in case I messed up
something last time.  Here's what I ran.  As you can see, it still
chooses to do a sequential scan.  Am I changing the stats for those
columns correctly?

Thanks,
Bryan


First, the query:


SELECT MAX(l.Id) as Id, l.ItemId
FROM ItemExperienceLog l
INNER JOIN Items_Primary p ON p.Id = l.ItemId
INNER JOIN Feeds f ON f.Id = p.FeedId
INNER JOIN Visitors v ON v.Id = l.VisitorId
WHERE
	v.UserId = 'fbe2537f21d94f519605612c0bf7c2c5'
	AND LOWER(f.Slug) = LOWER('Wealth_Building_by_NightingaleConant')
GROUP BY l.ItemId


The query plan:


HashAggregate  (cost=130291.23..130291.35 rows=9 width=37) (actual
time=8385.428..8385.433 rows=4 loops=1)
  ->  Nested Loop  (cost=2649.02..130291.19 rows=9 width=37) (actual
time=3707.336..8385.388 rows=11 loops=1)
        ->  Nested Loop  (cost=2649.02..129744.01 rows=1888 width=70)
(actual time=8.881..8322.029 rows=3210 loops=1)
              ->  Hash Join  (cost=2649.02..125273.81 rows=1888
width=37) (actual time=8.836..8196.469 rows=3210 loops=1)
                    Hash Cond: (l.visitorid = v.id)
                    ->  Seq Scan on itemexperiencelog l
(cost=0.00..112491.03 rows=2697303 width=70) (actual
time=0.048..4459.139 rows=2646177 loops=1)
                    ->  Hash  (cost=2631.24..2631.24 rows=1422
width=33) (actual time=7.751..7.751 rows=899 loops=1)
                          ->  Bitmap Heap Scan on visitors v
(cost=23.44..2631.24 rows=1422 width=33) (actual time=0.577..6.347
rows=899 loops=1)
                                Recheck Cond: (userid =
'fbe2537f21d94f519605612c0bf7c2c5'::bpchar)
                                ->  Bitmap Index Scan on
visitors_userid_index2  (cost=0.00..23.08 rows=1422 width=0) (actual
time=0.419..0.419 rows=899 loops=1)
                                      Index Cond: (userid =
'fbe2537f21d94f519605612c0bf7c2c5'::bpchar)
              ->  Index Scan using items_primary_pkey on items_primary
p  (cost=0.00..2.36 rows=1 width=66) (actual time=0.024..0.025 rows=1
loops=3210)
                    Index Cond: (p.id = l.itemid)
        ->  Index Scan using feeds_pkey on feeds f  (cost=0.00..0.28
rows=1 width=33) (actual time=0.018..0.018 rows=0 loops=3210)
              Index Cond: (f.id = p.feedid)
              Filter: (lower((f.slug)::text) =
'wealth_building_by_nightingaleconant'::text)
Total runtime: 8385.538 ms


Bump up the stats:


ALTER TABLE ItemExperienceLog ALTER COLUMN VisitorId SET STATISTICS 500;
ALTER TABLE ItemExperienceLog ALTER COLUMN ItemId SET STATISTICS 500;
ANALYZE ItemExperienceLog;

ALTER TABLE Visitors ALTER COLUMN UserId SET STATISTICS 500;
ALTER TABLE Visitors ALTER COLUMN Id SET STATISTICS 500;
ANALYZE Visitors;


The new query plan:


HashAggregate  (cost=127301.63..127301.72 rows=7 width=37) (actual
time=11447.033..11447.037 rows=4 loops=1)
  ->  Nested Loop  (cost=1874.67..127301.60 rows=7 width=37) (actual
time=4717.880..11446.987 rows=11 loops=1)
        ->  Nested Loop  (cost=1874.67..126923.09 rows=1306 width=70)
(actual time=20.565..11345.756 rows=3210 loops=1)
              ->  Hash Join  (cost=1874.67..123822.53 rows=1306
width=37) (actual time=20.445..8292.235 rows=3210 loops=1)
                    Hash Cond: (l.visitorid = v.id)
                    ->  Seq Scan on itemexperiencelog l
(cost=0.00..112010.04 rows=2646604 width=70) (actual
time=0.065..4438.481 rows=2646549 loops=1)
                    ->  Hash  (cost=1862.32..1862.32 rows=988
width=33) (actual time=19.360..19.360 rows=899 loops=1)
                          ->  Bitmap Heap Scan on visitors v
(cost=18.08..1862.32 rows=988 width=33) (actual time=0.666..17.788
rows=899 loops=1)
                                Recheck Cond: (userid =
'fbe2537f21d94f519605612c0bf7c2c5'::bpchar)
                                ->  Bitmap Index Scan on
visitors_userid_index2  (cost=0.00..17.83 rows=988 width=0) (actual
time=0.520..0.520 rows=899 loops=1)
                                      Index Cond: (userid =
'fbe2537f21d94f519605612c0bf7c2c5'::bpchar)
              ->  Index Scan using items_primary_pkey on items_primary
p  (cost=0.00..2.36 rows=1 width=66) (actual time=0.944..0.945 rows=1
loops=3210)
                    Index Cond: (p.id = l.itemid)
        ->  Index Scan using feeds_pkey on feeds f  (cost=0.00..0.28
rows=1 width=33) (actual time=0.029..0.029 rows=0 loops=3210)
              Index Cond: (f.id = p.feedid)
              Filter: (lower((f.slug)::text) =
'wealth_building_by_nightingaleconant'::text)
Total runtime: 11447.155 ms



view thread (14+ messages)  latest in thread

Message-ID: <7fd310d10903251514x1dd522f3o8d13a9218a6eda6c@mail.gmail.com>
Permalink:  ../7fd310d10903251514x1dd522f3o8d13a9218a6eda6c@mail.gmail.com/
Also on:    postgresql.org/message-id/7fd310d10903251514x1dd522f3o8d13a9218a6eda6c@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: bmurphy1976@gmail.com, josh@agliodbs.com
  Subject: Re: Help Me Understand Why I'm Getting a Bad Query Plan
  In-Reply-To: <7fd310d10903251514x1dd522f3o8d13a9218a6eda6c@mail.gmail.com>
  References: <7fd310d10903231203r1bd37b8cn8baeadae83253f44@mail.gmail.com> <7fd310d10903241521v1cbf5d7aueca653e03ed98649@mail.gmail.com> <b1b9fac60903242004p5fe8b68fwd6f1ada767766b21@mail.gmail.com> <7fd310d10903242047t1a7b7daw1d61c3de7f20b7c6@mail.gmail.com> <49CAA85C.8080402@agliodbs.com> <7fd310d10903251514x1dd522f3o8d13a9218a6eda6c@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox