DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=Qb7IQl4y+LBT4YSFuJY5OZMNndjOPF8+jNUUA3RLF4bWRea4K38r6a9vhyS3MoAvaF/NF9/8mirgeFq4loCtFNMwlgeTe0DxtFAZ6C3BHchNuaIcw/REWdn3RELm5Hs4A+tSt3MbfgQe1pOMSnhg7dHuDQtnP4ay9qB+fFu6xqI=;
References: 
 <CACfv+pLwqw-c=kR65WjA_pFy9M2qoKM2Usy77BoD8QWNwcfGHA@mail.gmail.com>
	<CACfv+pLPiufJWmjfL1+S8-6SOWZ28yo9M8GGcqkLC4JDJHhGGw@mail.gmail.com>
	<1365213250.66493.YahooMailNeo@web125901.mail.ne1.yahoo.com>
	<CACfv+pLTFEau=z5SVc1=93KxvjHux3UD2UZK2Cpj3AP8mYvhQw@mail.gmail.com>
Message-ID: <1365219176.2155.YahooMailNeo@web125904.mail.ne1.yahoo.com>
Date: Fri, 5 Apr 2013 20:32:56 -0700 (PDT)
From: Greg Williamson <gwilliamson39@yahoo.com>
Reply-To: Greg Williamson <gwilliamson39@yahoo.com>
Subject: Re: slow joins?
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
In-Reply-To: 
 <CACfv+pLTFEau=z5SVc1=93KxvjHux3UD2UZK2Cpj3AP8mYvhQw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


Joe --

>________________________________
> From: Joe Van Dyk <joe@tanga.com>
>To: Greg Williamson <gwilliamson39@yahoo.com>=20
>Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>=
=20
>Sent: Friday, April 5, 2013 7:56 PM
>Subject: Re: [PERFORM] slow joins?
>=20
>
>On Fri, Apr 5, 2013 at 6:54 PM, Greg Williamson <gwilliamson39@yahoo.com> =
wrote:
>
>Joe --
>>
>>>________________________________
>>> From: Joe Van Dyk <joe@tanga.com>
>>>To: pgsql-performance@postgresql.org
>>>Sent: Friday, April 5, 2013 6:42 PM
>>>Subject: Re: [PERFORM] slow joins?
>>
>>>
>>>
>>>(https://gist.github.com/joevandyk/df0df703f3fda6d14ae1/raw/c15cae813913=
b7f8c35b24b467a0c732c0100d79/gistfile1.txt shows a non-wrapped version of t=
he queries and plan)
>>>
>>>
>>>
>>>
>>>On Fri, Apr 5, 2013 at 6:38 PM, Joe Van Dyk <joe@tanga.com> wrote:
>>>
>>>On 9.2.4, running two identical queries except for the value of a column=
 in the WHERE clause. Postgres is picking very different query plans, the f=
irst is much slower than the second.
>>>>
>>>>
>>>>Any ideas on how I can speed this up? =A0I have btree indexes for all t=
he columns used in the query.
>>>>
>>>>explain analyze =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
>>>>SELECT COUNT(*) =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
>>>>FROM purchased_items pi =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
>>>>inner join line_items li on li.id =3D pi.line_item_id =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
 =A0
>>>>inner join products =A0 =A0 =A0on products.id =3D li.product_id =A0 =A0=
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0
>>>>WHERE products.drop_shipper_id =3D 221;
>>>>
>>>>=A0Aggregate =A0(cost=3D193356.31..193356.32 rows=3D1 width=3D0) (actua=
l time=3D2425.225..2425.225 rows=3D1 loops=3D1)
>>>>=A0 =A0-> =A0Hash Join =A0(cost=3D78864.43..193160.41 rows=3D78360 widt=
h=3D0) (actual time=3D726.612..2424.206 rows=3D8413 loops=3D1)
>>>>=A0 =A0 =A0 =A0 =A0Hash Cond: (pi.line_item_id =3D li.id)
>>>>=A0 =A0 =A0 =A0 =A0-> =A0Seq Scan on purchased_items pi =A0(cost=3D0.00=
..60912.39 rows=3D3724639 width=3D4) (actual time=3D0.008..616.812 rows=3D3=
724639 loops=3D1)
>>>>=A0 =A0 =A0 =A0 =A0-> =A0Hash =A0(cost=3D77937.19..77937.19 rows=3D5649=
9 width=3D4) (actual time=3D726.231..726.231 rows=3D8178 loops=3D1)
>>>>=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Buckets: 4096 =A0Batches: 4 =A0Memory Us=
age: 73kB
>>>>=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Hash Join =A0(cost=3D1684.33..7793=
7.19 rows=3D56499 width=3D4) (actual time=3D1.270..723.222 rows=3D8178 loop=
s=3D1)
>>>>=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Hash Cond: (li.product_id =
=3D products.id)
>>>>=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Seq Scan on line_items=
 li =A0(cost=3D0.00..65617.18 rows=3D2685518 width=3D8) (actual time=3D0.08=
1..392.926 rows=3D2685499 loops=3D1)
>>>>=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Hash =A0(cost=3D1676.6=
0..1676.60 rows=3D618 width=3D4) (actual time=3D0.835..0.835 rows=3D618 loo=
ps=3D1)
>>>>=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Buckets: 1024 =
=A0Batches: 1 =A0Memory Usage: 22kB
>>>>=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Bitmap Hea=
p Scan on products =A0(cost=3D13.07..1676.60 rows=3D618 width=3D4) (actual =
time=3D0.185..0.752 rows=3D618 loops=3D1)
>>>>=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Rech=
eck Cond: (drop_shipper_id =3D 221)
>>>>=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =
=A0Bitmap Index Scan on index_products_on_drop_shipper_id =A0(cost=3D0.00..=
12.92 rows=3D618 width=3D0) (actual time=3D0.125..0.125 rows=3D618 loops=3D=
1)
>>>>=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
 =A0 =A0Index Cond: (drop_shipper_id =3D 221)
>>>>=A0Total runtime: 2425.302 ms
>>>>
>>>>
>>>>explain analyze =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
>>>>SELECT COUNT(*) =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
>>>>FROM purchased_items pi =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
>>>>inner join line_items li on li.id =3D pi.line_item_id =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
 =A0
>>>>inner join products =A0 =A0 =A0on products.id =3D li.product_id =A0 =A0=
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0
>>>>WHERE products.drop_shipper_id =3D 2; =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
 =A0 =A0 =A0 =A0 =A0
>>>>=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
 =A0 =A0 =A0 =A0
>>>>
>>>>=A0Aggregate =A0(cost=3D29260.40..29260.41 rows=3D1 width=3D0) (actual =
time=3D0.906..0.906 rows=3D1 loops=3D1)
>>>>=A0 =A0-> =A0Nested Loop =A0(cost=3D0.00..29254.38 rows=3D2409 width=3D=
0) (actual time=3D0.029..0.877 rows=3D172 loops=3D1)
>>>>=A0 =A0 =A0 =A0 =A0-> =A0Nested Loop =A0(cost=3D0.00..16011.70 rows=3D1=
737 width=3D4) (actual time=3D0.021..0.383 rows=3D167 loops=3D1)
>>>>=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Index Scan using index_products_on=
_drop_shipper_id on products =A0(cost=3D0.00..80.41 rows=3D19 width=3D4) (a=
ctual time=3D0.010..0.074 rows=3D70 loops=3D1)
>>>>=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (drop_shipper_id=
 =3D 2)
>>>>=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Index Scan using index_line_items_=
on_product_id on line_items li =A0(cost=3D0.00..835.70 rows=3D279 width=3D8=
) (actual time=3D0.002..0.004 rows=3D2 loops=3D70)
>>>>=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (product_id =3D =
products.id)
>>>>=A0 =A0 =A0 =A0 =A0-> =A0Index Only Scan using purchased_items_line_ite=
m_id_idx on purchased_items pi =A0(cost=3D0.00..7.60 rows=3D2 width=3D4) (a=
ctual time=3D0.002..0.003 rows=3D1 loops=3D167)
>>>>=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (line_item_id =3D li.id)
>>>>=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Heap Fetches: 5
>>>>=A0Total runtime: 0.955 ms
>>>>(11 rows)
>>>>
>>>
>>
>>
>>Does drop_shipper+id have a much larger number of rows which is making th=
e scanner want to avoid an indexed scan or otherwise prefer a sequential sc=
an on products and on line_items ?
>>
>
>
>Assuming you mean products.drop_shipper_id? There are more rows matched fo=
r the first one vs the second one.=A0
>70 products rows match drop_shipper_id=3D2, 618 match drop_shipper_id=3D22=
1.
>=A0
>What are the stats settings for these tables ?
>>
>
>
>Whatever the defaults are.
>=A0

I mis-pasted the tables -- both line_items and purchased items are getting =
sequential scans for the relevant rows; it is possible that that there's en=
ough difference to tip the planner to use sequential scans.

You might try increasing the stats being collected on those two tables, run=
 analyze on all the tables in the query, and try it again.

GW


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance