From: "Julien Theulier" <julien@squidsolutions.com>
To: "'Joshua Tolley'" <eggyknap@gmail.com>
Cc: <pgsql-performance@postgresql.org>
References: <gfcprb$m40$1@news.hub.org> <4919F2C6.4000303@fuzzy.cz>
	<003c01c944c9$c1d92ac0$458b8040$@com>
	<20081112135405.GD22032@polonium.part.net>
In-Reply-To: <20081112135405.GD22032@polonium.part.net>
Subject: Re: Index usage with sub select or outer joins
Date: Wed, 12 Nov 2008 16:09:35 +0100
Organization: SQUID Solutions
Message-ID: <004d01c944d8$ae1b01c0$0a510540$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Thread-Index: AclEzh+MkC6WwQ42QamY0D3E1Dl4XAABOafA
Content-Language: fr

Hello, Joshua,

I did different test cases and here are the results (numbers in =
seconds),
using (case sub queries) or not (case join) the index:
Rows (main table)	Outer Join		Sub queries
setting
1396163 rows	39.2			19.6
work_mem=3D256Mb
3347443 rows 	72.2			203.1
work_mem=3D256Mb
3347443 rows 	70.3			31.1
work_mem=3D1024Mb
4321072 rows 	115			554.9
work_mem=3D256Mb
4321072 rows 	111			583
work_mem=3D1024Mb
All outer joins where done without index uses

To force the use of the index for the first case (outer join), I have =
change
the seq_scan cost (from 1 to 2.5), it takes now only 6.1s for the outer =
join
on 1.4M rows. New explain plan below:
"HashAggregate  (cost=3D457881.84..460248.84 rows=3D39450 width=3D49)"
"  ->  Nested Loop Left Join  (cost=3D0.00..456994.22 rows=3D39450 =
width=3D49)"
"        ->  Seq Scan on bm_us_views_main_2608 a  =
(cost=3D0.00..223677.45
rows=3D39450 width=3D41)"
"              Filter: ((item_type =3D ANY ('{7,9}'::numeric[])) AND =
(qty >
1))"
"        ->  Index Scan using bm_us_bids_item_ix on bm_us_bids b
(cost=3D0.00..5.65 rows=3D13 width=3D19)"
"              Index Cond: ((b.item_id =3D a.item_id) AND (b.bid_date <
a.pv_timestamp) AND (b.bid_date >=3D (a.pv_timestamp - '60 =
days'::interval)))"

Index bm_us_bids_item_ix is on item_id, bidder_id (not used in the
condition) & bid_date

What can be the recommendations on tuning the different costs so it can
better estimate the seq scan & index scans costs? I think the issue is
there. But didn't find any figures helping to choose the correct =
parameters
according to cpu & disks speed

Regards,
Julien Theulier

-----Message d'origine-----
De=A0: Joshua Tolley [mailto:eggyknap@gmail.com]=20
Envoy=E9=A0: mercredi 12 novembre 2008 14:54
=C0=A0: Julien Theulier
Cc=A0: pgsql-performance@postgresql.org
Objet=A0: Re: [PERFORM] Index usage with sub select or inner joins

On Wed, Nov 12, 2008 at 02:22:47PM +0100, Julien Theulier wrote:
> QUESTION: Why the planner choose seq scan in the first case & indexes=20
> scan in the second case? In a more general way, I observed that the=20
> planner has difficulties to select index scans & does in almost all=20
> the cases seq scan, when doing join queries. After investigations, it=20
> looks like when you join table a with table b on a column x and y and=20
> you have an index on column x only, the planner is not able to choose=20
> the index scan. You have to build the index corresponding exactly to=20
> the join statement btw the 2 tables

Short, general answer: index scans aren't always faster than sequential
scans, and the planner is smart enough to know that. Googling "Why isn't
postgresql using my index" provides more detailed results, but in short, =
if
it scans an index, it has to read pages from the index, and for all the
tuples it finds in the index, it has to read once again from the heap,
whereas a sequential scan requires reading once from the heap. If your =
query
will visit most of the rows of the table, pgsql will choose a sequential
scan over an index scan.

- Josh / eggyknap