DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:content-type:content-transfer-encoding;
	b=AwFbRlQgwcCeFKGYk6fHsIIqrKZWYp6icyUMc8nrYNHqeEeQ5bDfc73fU19+e4skqu
	tMdq2W8mEviz6Hh62uhY+CPMH+kJn7Yi6JBB4AHK/VYlw2VcvbP8nS9gsQi4xCk/7naf
	wvS+FDBAHdoUfYwFT1SKxVrjEYpcIQcEVF5NM=
MIME-Version: 1.0
In-Reply-To: <7fd310d10903231203r1bd37b8cn8baeadae83253f44@mail.gmail.com>
References: <7fd310d10903231203r1bd37b8cn8baeadae83253f44@mail.gmail.com>
Date: Tue, 24 Mar 2009 17:21:28 -0500
Message-ID: <7fd310d10903241521t645462bdpb3b8aee40b4f3ae4@mail.gmail.com>
Subject: Re: Help Me Understand Why I'm Getting a Bad Query Plan
From: Bryan Murphy <bmurphy1976@gmail.com>
To: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

I hate to nag, but could anybody help me with this?  We have a few
related queries that are causing noticeable service delays in our
production system.  I've tried a number of different things, but I'm
running out of ideas and don't know what to do next.

Thanks,
Bryan

On Mon, Mar 23, 2009 at 2:03 PM, Bryan Murphy <bmurphy1976@gmail.com> wrote=
:
> Hey Guys,
>
> I've got a query on our production system that isn't choosing a good
> plan. =A0I can't see why it's choosing to do a sequential scan on the
> ItemExperienceLog table. =A0That table is about 800mb and has about 2.5
> million records. =A0This example query only returns 4 records. =A0I've
> tried upping the statics for ItemExperienceLog.VistorId and
> ItemExperienceLog.ItemId to 1000 (from out default of 100) with no
> success.
>
> Our primary keys are guids stored as char(32) not null. =A0Our database
> uses UTF-8 encoding and is currently version v8.3.5.
>
> The queries:
>
>
>
> --SET enable_seqscan =3D off
> --SET enable_seqscan =3D on
>
> --ALTER TABLE ItemExperienceLog ALTER COLUMN VisitorId SET STATISTICS 100=
0
> --ALTER TABLE ItemExperienceLog ALTER COLUMN ItemId SET STATISTICS 1000
> --ANALYZE ItemExperienceLog
>
> SELECT MAX(l.Id) as Id, l.ItemId
> FROM ItemExperienceLog l
> INNER JOIN Items_Primary p ON p.Id =3D l.ItemId
> INNER JOIN Feeds f ON f.Id =3D p.FeedId
> INNER JOIN Visitors v ON v.Id =3D l.VisitorId
> WHERE
> =A0 =A0 =A0 =A0v.UserId =3D 'fbe2537f21d94f519605612c0bf7c2c5'
> =A0 =A0 =A0 =A0AND LOWER(f.Slug) =3D LOWER('Wealth_Building_by_Nightingal=
eConant')
> GROUP BY l.ItemId
>
>
>
> Explain verbose output (set enable_seqscan =3D on):
>
>
>
> HashAggregate =A0(cost=3D124392.54..124392.65 rows=3D9 width=3D37) (actua=
l
> time=3D7765.650..7765.654 rows=3D4 loops=3D1)
> =A0-> =A0Nested Loop =A0(cost=3D2417.68..124392.49 rows=3D9 width=3D37) (=
actual
> time=3D1706.703..7765.611 rows=3D11 loops=3D1)
> =A0 =A0 =A0 =A0-> =A0Nested Loop =A0(cost=3D2417.68..123868.75 rows=3D180=
7 width=3D70)
> (actual time=3D36.374..7706.677 rows=3D3174 loops=3D1)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Hash Join =A0(cost=3D2417.68..119679.50 =
rows=3D1807
> width=3D37) (actual time=3D36.319..7602.221 rows=3D3174 loops=3D1)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Hash Cond: (l.visitorid =3D v.id)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Seq Scan on itemexperiencelo=
g l
> (cost=3D0.00..107563.09 rows=3D2581509 width=3D70) (actual
> time=3D0.010..4191.251 rows=3D2579880 loops=3D1)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Hash =A0(cost=3D2401.43..240=
1.43 rows=3D1300
> width=3D33) (actual time=3D3.673..3.673 rows=3D897 loops=3D1)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Bitmap Heap Scan=
 on visitors v
> (cost=3D22.48..2401.43 rows=3D1300 width=3D33) (actual time=3D0.448..2.52=
3
> rows=3D897 loops=3D1)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Recheck Co=
nd: (userid =3D
> 'fbe2537f21d94f519605612c0bf7c2c5'::bpchar)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Bitm=
ap Index Scan on
> visitors_userid_index2 =A0(cost=3D0.00..22.16 rows=3D1300 width=3D0) (act=
ual
> time=3D0.322..0.322 rows=3D897 loops=3D1)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0Index Cond: (userid =3D
> 'fbe2537f21d94f519605612c0bf7c2c5'::bpchar)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Index Scan using items_primary_pkey on i=
tems_primary
> p =A0(cost=3D0.00..2.31 rows=3D1 width=3D66) (actual time=3D0.027..0.029 =
rows=3D1
> loops=3D3174)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (p.id =3D l.itemid)
> =A0 =A0 =A0 =A0-> =A0Index Scan using feeds_pkey on feeds f =A0(cost=3D0.=
00..0.28
> rows=3D1 width=3D33) (actual time=3D0.016..0.016 rows=3D0 loops=3D3174)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (f.id =3D p.feedid)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: (lower((f.slug)::text) =3D
> 'wealth_building_by_nightingaleconant'::text)
> Total runtime: 7765.767 ms
>
>
>
> Explain verbose output (set enable_seqscan =3D off):
>
>
>
> HashAggregate =A0(cost=3D185274.71..185274.82 rows=3D9 width=3D37) (actua=
l
> time=3D185.024..185.028 rows=3D4 loops=3D1)
> =A0-> =A0Nested Loop =A0(cost=3D0.00..185274.67 rows=3D9 width=3D37) (act=
ual
> time=3D0.252..184.989 rows=3D11 loops=3D1)
> =A0 =A0 =A0 =A0-> =A0Nested Loop =A0(cost=3D0.00..184751.21 rows=3D1806 w=
idth=3D70)
> (actual time=3D0.223..134.943 rows=3D3174 loops=3D1)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Nested Loop =A0(cost=3D0.00..180564.28 r=
ows=3D1806
> width=3D37) (actual time=3D0.192..60.214 rows=3D3174 loops=3D1)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Index Scan using visitors_us=
erid_index2 on
> visitors v =A0(cost=3D0.00..2580.97 rows=3D1300 width=3D33) (actual
> time=3D0.052..2.342 rows=3D897 loops=3D1)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (userid =
=3D
> 'fbe2537f21d94f519605612c0bf7c2c5'::bpchar)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Index Scan using
> itemexperiencelog__index__visitorid on itemexperiencelog l
> (cost=3D0.00..134.04 rows=3D230 width=3D70) (actual time=3D0.013..0.040 r=
ows=3D4
> loops=3D897)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (l.visitor=
id =3D v.id)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Index Scan using items_primary_pkey on i=
tems_primary
> p =A0(cost=3D0.00..2.31 rows=3D1 width=3D66) (actual time=3D0.019..0.020 =
rows=3D1
> loops=3D3174)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (p.id =3D l.itemid)
> =A0 =A0 =A0 =A0-> =A0Index Scan using feeds_pkey on feeds f =A0(cost=3D0.=
00..0.28
> rows=3D1 width=3D33) (actual time=3D0.014..0.014 rows=3D0 loops=3D3174)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (f.id =3D p.feedid)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: (lower((f.slug)::text) =3D
> 'wealth_building_by_nightingaleconant'::text)
> Total runtime: 185.117 ms
>
>
>
> The relevent portions of postgresql.conf:
>
>
> max_connections =3D 100
> shared_buffers =3D 2GB
> temp_buffers =3D 32MB
> work_mem =3D 64MB
> maintenance_work_mem =3D 256MB
> max_stack_depth =3D 8MB
> wal_buffers =3D 1MB
> checkpoint_segments =3D 32
> random_page_cost =3D 2.0
> effective_cache_size =3D 12GB
> default_statistics_target =3D 100
>
>
>
> The tables and the indexes that matter:
>
>
>
> CREATE TABLE itemexperiencelog
> (
> =A0id integer NOT NULL DEFAULT nextval('itemexperiencelog__sequence'::reg=
class),
> =A0visitorid character(32) NOT NULL,
> =A0itemid character(32) NOT NULL,
> =A0created timestamp without time zone NOT NULL,
> =A0modified timestamp without time zone NOT NULL,
> =A0"position" integer NOT NULL DEFAULT 0,
> =A0itemlength integer NOT NULL DEFAULT 0,
> =A0usercomplete boolean NOT NULL DEFAULT false,
> =A0contentcomplete boolean NOT NULL DEFAULT false,
> =A0source character varying(32) NOT NULL,
> =A0sessionid character(32) NOT NULL,
> =A0authenticatedatcreation boolean NOT NULL DEFAULT false,
> =A0CONSTRAINT itemexperiencelog_pkey PRIMARY KEY (id),
> =A0CONSTRAINT itemexperiencelog_itemid_fkey FOREIGN KEY (itemid)
> =A0 =A0 =A0REFERENCES items_primary (id) MATCH SIMPLE
> =A0 =A0 =A0ON UPDATE NO ACTION ON DELETE NO ACTION
> )
>
> CREATE INDEX itemexperiencelog__index__itemid
> =A0ON itemexperiencelog
> =A0USING btree
> =A0(itemid);
>
> CREATE INDEX itemexperiencelog__index__visitorid
> =A0ON itemexperiencelog
> =A0USING btree
> =A0(visitorid);
>
>
>
> CREATE TABLE items_primary
> (
> =A0id character(32) NOT NULL,
> =A0feedid character(32),
> =A0slug character varying(255),
> =A0pubdate timestamp without time zone,
> =A0isvisible boolean,
> =A0deleted integer,
> =A0itemgroupid integer,
> =A0lockedflags integer NOT NULL,
> =A0CONSTRAINT items_primary_pkey PRIMARY KEY (id),
> =A0CONSTRAINT items_itemgroupid_fkey FOREIGN KEY (itemgroupid)
> =A0 =A0 =A0REFERENCES itemgroups (id) MATCH SIMPLE
> =A0 =A0 =A0ON UPDATE NO ACTION ON DELETE NO ACTION,
> =A0CONSTRAINT items_primary_feedid_fkey FOREIGN KEY (feedid)
> =A0 =A0 =A0REFERENCES feeds (id) MATCH SIMPLE
> =A0 =A0 =A0ON UPDATE NO ACTION ON DELETE NO ACTION
> )
> WITH (OIDS=3DFALSE);
>
> CREATE INDEX items_primary_feedid_index
> =A0ON items_primary
> =A0USING btree
> =A0(feedid);
>
>
>
> CREATE TABLE feeds
> (
> =A0id character(32) NOT NULL,
> =A0rssupdateinterval integer,
> =A0lastrssupdate timestamp without time zone,
> =A0nextrssupdate timestamp without time zone,
> =A0url text NOT NULL,
> =A0title text NOT NULL,
> =A0subtitle text,
> =A0link text,
> =A0slug character varying(2048) NOT NULL,
> =A0description text,
> =A0lang character varying(255),
> =A0copyright text,
> =A0pubdate timestamp without time zone,
> =A0lastbuilddate character varying(255),
> =A0docs text,
> =A0generator character varying(255),
> =A0managingeditor character varying(255),
> =A0webmaster character varying(255),
> =A0status integer,
> =A0ttl character varying(255),
> =A0image_title text,
> =A0image_url text,
> =A0image_link text,
> =A0image_description text,
> =A0image_width integer,
> =A0image_height integer,
> =A0rating character varying(255),
> =A0skiphours character varying(255),
> =A0skipdays character varying(255),
> =A0genretagid character(32),
> =A0sourceuserid character(32),
> =A0created timestamp without time zone NOT NULL,
> =A0deleted integer NOT NULL,
> =A0cloud_domain character varying(255),
> =A0cloud_port character varying(255),
> =A0cloud_path character varying(255),
> =A0cloud_registerprocedure character varying(255),
> =A0cloud_protocol character varying(255),
> =A0itunesauthor character varying(255),
> =A0itunesblock character varying(255),
> =A0itunescategories text,
> =A0itunesimage character varying(255),
> =A0itunesexplicit character varying(255),
> =A0ituneskeywords text,
> =A0itunesnewfeedurl character varying(255),
> =A0itunesowner_name character varying(255),
> =A0itunesowner_email character varying(255),
> =A0itunessubtitle text,
> =A0itunessummary text,
> =A0yahooimage text,
> =A0modified timestamp without time zone NOT NULL,
> =A0mediatype integer,
> =A0isvisible boolean NOT NULL DEFAULT false,
> =A0mediaplayertype integer NOT NULL,
> =A0episodebrowsecount integer,
> =A0comments text NOT NULL DEFAULT ''::text,
> =A0lockedflags integer NOT NULL DEFAULT 0,
> =A0sequenceid integer NOT NULL DEFAULT nextval('feeds_sequence'::regclass=
),
> =A0feedgroupid character(32),
> =A0bannerurl text,
> =A0categories text NOT NULL,
> =A0publisher text,
> =A0episodefetchstrategy integer NOT NULL,
> =A0averageuserrating real,
> =A0userratingscount integer,
> =A0lastupdate timestamp without time zone NOT NULL,
> =A0userratingstotal double precision NOT NULL,
> =A0CONSTRAINT feeds_pkey PRIMARY KEY (id),
> =A0CONSTRAINT feeds_feedgroupid_fkey FOREIGN KEY (feedgroupid)
> =A0 =A0 =A0REFERENCES feedgroups (id) MATCH SIMPLE
> =A0 =A0 =A0ON UPDATE NO ACTION ON DELETE NO ACTION,
> =A0CONSTRAINT fk5cb07d0e1080d15d FOREIGN KEY (sourceuserid)
> =A0 =A0 =A0REFERENCES users (id) MATCH SIMPLE
> =A0 =A0 =A0ON UPDATE NO ACTION ON DELETE NO ACTION,
> =A0CONSTRAINT fk5cb07d0eb113835f FOREIGN KEY (genretagid)
> =A0 =A0 =A0REFERENCES tags (id) MATCH SIMPLE
> =A0 =A0 =A0ON UPDATE NO ACTION ON DELETE NO ACTION,
> =A0CONSTRAINT feeds_slug_key UNIQUE (slug)
> )
> WITH (OIDS=3DFALSE);
>
> CREATE UNIQUE INDEX feeds_slug_unique
> =A0ON feeds
> =A0USING btree
> =A0(lower(slug::text));
>
>
>
> CREATE TABLE visitors
> (
> =A0id character(32) NOT NULL,
> =A0visitorid character(32) NOT NULL,
> =A0userid character(32),
> =A0sessionid text NOT NULL,
> =A0created timestamp without time zone NOT NULL,
> =A0modified timestamp without time zone NOT NULL,
> =A0deleted integer NOT NULL,
> =A0sequenceid integer DEFAULT nextval('visitors_sequence'::regclass),
> =A0CONSTRAINT visitors_pkey PRIMARY KEY (id)
> )
> WITH (OIDS=3DFALSE);
>
> CREATE INDEX visitors_userid_index2
> =A0ON visitors
> =A0USING btree
> =A0(userid);
>
> CREATE INDEX visitors_visitorid_index2
> =A0ON visitors
> =A0USING btree
> =A0(visitorid);
>