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 ED24C9DC867 for ; Fri, 6 Jan 2006 16:24:12 -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 92288-07 for ; Fri, 6 Jan 2006 16:24:13 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 456599DC816 for ; Fri, 6 Jan 2006 16:24:09 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k06KO9vj022454; Fri, 6 Jan 2006 15:24:09 -0500 (EST) To: Patrick Hatcher cc: pgsql-performance@postgresql.org Subject: Re: Slow query. Any way to speed up? In-reply-to: References: Comments: In-reply-to Patrick Hatcher message dated "Fri, 06 Jan 2006 10:55:28 -0800" Date: Fri, 06 Jan 2006 15:24:09 -0500 Message-ID: <22453.1136579049@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.072 required=5 tests=[AWL=0.072] X-Spam-Score: 0.072 X-Spam-Level: X-Archive-Number: 200601/43 X-Sequence-Number: 16521 Patrick Hatcher writes: > -> Seq Scan on cdm_ddw_tran_item a1 > (cost=0.00..1547562.88 rows=8754773 width=23) (actual > time=14.219..535704.691 rows=10838135 loops=1) > Filter: ((((appl_id)::text = 'MCOM'::text) > OR ((appl_id)::text = 'NET'::text)) AND ((tran_typ_id = 'S'::bpchar) OR > (tran_typ_id = 'R'::bpchar))) The bulk of the time is evidently going into this step. You didn't say how big cdm_ddw_tran_item is, but unless it's in the billion-row range, an indexscan isn't going to help for pulling out 10 million rows. This may be about the best you can do :-( If it *is* in the billion-row range, PG 8.1's bitmap indexscan facility would probably help. regards, tom lane