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 B91A29DCA2F for ; Wed, 14 Dec 2005 19:23:22 -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 19741-07 for ; Wed, 14 Dec 2005 19:23:24 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.193]) by postgresql.org (Postfix) with ESMTP id C43399DCA29 for ; Wed, 14 Dec 2005 19:23:17 -0400 (AST) Received: by wproxy.gmail.com with SMTP id 57so234134wri for ; Wed, 14 Dec 2005 15:23:20 -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=EuCL0mx1FtGlZgddrUgWI59O/oh4fnsp4RT6MDLFB6xFJRdv5HROeFx1kOo4plyvHhBVvBpqA8VmyoNnQ/hu3Ho52ZBS3LhzK1W+ZX/EMYuOmHQs/mHOZs6CFN4jw8cp+SU8m9PnJI728S53G6GrrKikXocwLGHzMcWr4g58SN8= Received: by 10.54.127.3 with SMTP id z3mr1405454wrc; Wed, 14 Dec 2005 15:23:20 -0800 (PST) Received: by 10.54.98.6 with HTTP; Wed, 14 Dec 2005 15:23:20 -0800 (PST) Message-ID: Date: Wed, 14 Dec 2005 18:23:20 -0500 From: Jaime Casanova To: Kevin Brown Subject: Re: Simple Join Cc: pgsql-performance@postgresql.org In-Reply-To: <200512141603.52583.blargity@gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <200512141603.52583.blargity@gmail.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.105 required=5 tests=[AWL=0.105] X-Spam-Score: 0.105 X-Spam-Level: X-Archive-Number: 200512/339 X-Sequence-Number: 16160 On 12/14/05, Kevin Brown wrote: > I'll just start by warning that I'm new-ish to postgresql. > > I'm running 8.1 installed from source on a Debian Sarge server. I have a > simple query that I believe I've placed the indexes correctly for, and I > still end up with a seq scan. It makes sense, kinda, but it should be ab= le > to use the index to gather the right values. I do have a production set = of > data inserted into the tables, so this is running realistically: > what hardware? > dli=3D# explain analyze SELECT ordered_products.product_id > dli-# FROM to_ship, ordered_products > dli-# WHERE to_ship.ordered_product_id =3D ordered_products.id AND > dli-# ordered_products.paid =3D TRUE AND > dli-# ordered_products.suspended_sub =3D FALSE; > QUERY PLAN > -------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------ > Hash Join (cost=3D5126.19..31528.40 rows=3D20591 width=3D8) (actual > time=3D6517.438..25123.115 rows=3D14367 loops=3D1) > Hash Cond: ("outer".ordered_product_id =3D "inner".id) > -> Seq Scan on to_ship (cost=3D0.00..11529.12 rows=3D611612 width=3D8= ) (actual > time=3D393.206..15711.715 rows=3D611612 loops=3D1) > -> Hash (cost=3D4954.79..4954.79 rows=3D21759 width=3D16) (actual > time=3D6076.153..6076.153 rows=3D18042 loops=3D1) > -> Index Scan using paid_index on ordered_products > (cost=3D0.00..4954.79 rows=3D21759 width=3D16) (actual time=3D136.472..59= 66.275 > rows=3D18042 loops=3D1) > Index Cond: (paid =3D true) > Filter: (paid AND (NOT suspended_sub)) > Total runtime: 25136.190 ms > (8 rows) > show the tables and the indexes for those tables > This is running on just about the world's slowest server (with a laptop h= ard > drive to boot), but how can I avoid the seq scan, or in general speed up = this > query? > > to_ship will have far less tuples than ordered_products, but it's still n= ot > small, as you can see. > -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;)