X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 3EC7911F66F5 for ; Fri, 31 Mar 2006 21:23:39 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 70969-07 for ; Fri, 31 Mar 2006 21:23:38 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.193]) by postgresql.org (Postfix) with ESMTP id DADB211F660F for ; Fri, 31 Mar 2006 21:23:36 -0400 (AST) Received: by zproxy.gmail.com with SMTP id m7so83426nzf for ; Fri, 31 Mar 2006 17:23:37 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=Jaw9EIHwvY9zn5ElYr9q1LToY6PHAk2t2J94w3WCWUqbMylz1BWLjcr3a+tTyQtQM/AhtQFkDEbl0u2NLlSJ3m6Txg6ockgSqMRgXUy1q9KmKeN6Zm583SVXC1/XEwCWZq2tWH14UK7rSVMHKOOyLMpURFBYqWzOVOnaKLtlvzM= Received: by 10.37.18.44 with SMTP id v44mr1371203nzi; Fri, 31 Mar 2006 17:23:37 -0800 (PST) Received: by 10.36.196.1 with HTTP; Fri, 31 Mar 2006 17:23:37 -0800 (PST) Message-ID: <3c1395330603311723i368092f2jf0c2291ab033ae2a@mail.gmail.com> Date: Sat, 1 Apr 2006 11:23:37 +1000 From: "chris smith" To: "Brendan Duddridge" Subject: Re: Query using SeqScan instead of IndexScan Cc: "Jim C. Nasby" , "PostgreSQL Performance" In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <2F1712F1-E5E2-455E-A291-06F300A66549@clickspace.com> <20060331155911.GP49405@pervasive.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.13 required=5 tests=[AWL=0.130] X-Spam-Score: 0.13 X-Spam-Level: X-Archive-Number: 200603/646 X-Sequence-Number: 18083 On 4/1/06, Brendan Duddridge wrote: > Hi Jim, > > I'm not quite sure what you mean by the correlation of category_id? It means how many distinct values does it have (at least that's my understanding of it ;) ). select category_id, count(*) from category_product group by category_id; will show you how many category_id's there are and how many products are in each category. Having a lot of products in one category (or having a small amount of categories) can slow things down because the db can't use the index effectively.. which might be what you're seeing (hence why it's fast for some categories, slow for others). > On Mar 31, 2006, at 8:59 AM, Jim C. Nasby wrote: > > > What's the correlation of category_id? The current index scan cost > > estimator places a heavy penalty on anything with a correlation much > > below about 90%. > > > > On Wed, Mar 29, 2006 at 08:12:28PM -0700, Brendan Duddridge wrote: > >> Hi, > >> > >> I have a query that is using a sequential scan instead of an index > >> scan. I've turned off sequential scans and it is in fact faster with > >> the index scan. > >> > >> Here's my before and after. > >> > >> Before: > >> > >> ssdev=3D# SET enable_seqscan TO DEFAULT; > >> ssdev=3D# explain analyze select cp.product_id > >> from category_product cp, product_attribute_value pav > >> where cp.category_id =3D 1001082 and cp.product_id =3D > >> pav.product_id; > >> > >> > >> QUERY PLAN > >> --------------------------------------------------------------------- > >> --- > >> --------------------------------------------------------------------- > >> --- > >> ------------------------------ > >> Hash Join (cost=3D25.52..52140.59 rows=3D5139 width=3D4) (actual > >> time=3D4.521..2580.520 rows=3D19695 loops=3D1) > >> Hash Cond: ("outer".product_id =3D "inner".product_id) > >> -> Seq Scan on product_attribute_value pav (cost=3D0.00..40127.12 > >> rows=3D2387312 width=3D4) (actual time=3D0.039..1469.295 rows=3D238584= 6 > >> loops=3D1) > >> -> Hash (cost=3D23.10..23.10 rows=3D970 width=3D4) (actual > >> time=3D2.267..2.267 rows=3D1140 loops=3D1) > >> -> Index Scan using x_category_product__category_id_fk_idx > >> on category_product cp (cost=3D0.00..23.10 rows=3D970 width=3D4) (act= ual > >> time=3D0.122..1.395 rows=3D1140 loops=3D1) > >> Index Cond: (category_id =3D 1001082) > >> Total runtime: 2584.221 ms > >> (7 rows) > >> > >> > >> After: > >> > >> ssdev=3D# SET enable_seqscan TO false; > >> ssdev=3D# explain analyze select cp.product_id > >> from category_product cp, product_attribute_value pav > >> where cp.category_id =3D 1001082 and cp.product_id =3D > >> pav.product_id; > >> > >> > >> QUERY PLAN > >> --------------------------------------------------------------------- > >> --- > >> --------------------------------------------------------------------- > >> --- > >> ------------------------------------- > >> Nested Loop (cost=3D0.00..157425.22 rows=3D5139 width=3D4) (actual > >> time=3D0.373..71.177 rows=3D19695 loops=3D1) > >> -> Index Scan using x_category_product__category_id_fk_idx on > >> category_product cp (cost=3D0.00..23.10 rows=3D970 width=3D4) (actual > >> time=3D0.129..1.438 rows=3D1140 loops=3D1) > >> Index Cond: (category_id =3D 1001082) > >> -> Index Scan using product_attribute_value__product_id_fk_idx > >> on product_attribute_value pav (cost=3D0.00..161.51 rows=3D61 width= =3D4) > >> (actual time=3D0.016..0.053 rows=3D17 loops=3D1140) > >> Index Cond: ("outer".product_id =3D pav.product_id) > >> Total runtime: 74.747 ms > >> (6 rows) > >> > >> There's quite a big difference in speed there. 2584.221 ms vs. 74.747 > >> ms. > >> > >> Any ideas what I can do to improve this without turning sequential > >> scanning off? > >> > >> Thanks, > >> > >> ____________________________________________________________________ > >> Brendan Duddridge | CTO | 403-277-5591 x24 | brendan@clickspace.com > >> > >> ClickSpace Interactive Inc. > >> Suite L100, 239 - 10th Ave. SE > >> Calgary, AB T2G 0V9 > >> > >> http://www.clickspace.com > >> > > > > > > > > -- > > Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com > > Pervasive Software http://pervasive.com work: 512-231-6117 > > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 1: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that > > your > > message can get through to the mailing list cleanly > > > > > > -- Postgresql & php tutorials http://www.designmagick.com/