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 B8F789DC845 for ; Fri, 6 Jan 2006 05:56:31 -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 71312-07 for ; Fri, 6 Jan 2006 05:56:33 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.200]) by postgresql.org (Postfix) with ESMTP id 35D6F9DCAEF for ; Fri, 6 Jan 2006 05:56:28 -0400 (AST) Received: by zproxy.gmail.com with SMTP id 14so3489703nzn for ; Fri, 06 Jan 2006 01:56:31 -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=qaVVnvUn6e34GuBvIa2jydHHWsbxm7V+ustea2kOPKeD9lFFtnItGgvZcPKQMAJHI55SdMyUy1o8LW0Tqni7V2LdK5xxq9ohYd5h/LEks13+chcR36UYarbzcy64lCRdIUBrQf3etHes+RsnDbkcg+OOkK3fsdLqlSZydm79nAc= Received: by 10.64.150.20 with SMTP id x20mr1731625qbd; Fri, 06 Jan 2006 01:56:31 -0800 (PST) Received: by 10.64.210.15 with HTTP; Fri, 6 Jan 2006 01:56:31 -0800 (PST) Message-ID: <5e744e3d0601060156y3f68ad0dn152c946254ee9fd3@mail.gmail.com> Date: Fri, 6 Jan 2006 15:26:31 +0530 From: Pandurangan R S To: Andy Subject: Re: Improving Inner Join Performance Cc: pgsql-performance@postgresql.org In-Reply-To: <001d01c6120b$0d783600$0b00a8c0@forge> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <001d01c6120b$0d783600$0b00a8c0@forge> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.124 required=5 tests=[AWL=0.124] X-Spam-Score: 0.124 X-Spam-Level: X-Archive-Number: 200601/35 X-Sequence-Number: 16513 > If the users puts in some other search fields on the where then the query= runs faster but > in this format sometimes it takes a lot lot of time(some= times even 2,3 seconds). Can you eloborate under what conditions which query is slower? On 1/5/06, Andy wrote: > > Hi to all, > > I have the following query: > > SELECT count(*) FROM orders o > INNER JOIN report r ON r.id_order=3Do.id > WHERE o.id_status>3 > > Explaing analyze: > Aggregate (cost=3D8941.82..8941.82 rows=3D1 width=3D0) (actual > time=3D1003.297..1003.298 rows=3D1 loops=3D1) > -> Hash Join (cost=3D3946.28..8881.72 rows=3D24041 width=3D0) (actual > time=3D211.985..951.545 rows=3D72121 loops=3D1) > Hash Cond: ("outer".id_order =3D "inner".id) > -> Seq Scan on report r (cost=3D0.00..2952.21 rows=3D72121 widt= h=3D4) > (actual time=3D0.005..73.869 rows=3D72121 loops=3D1) > -> Hash (cost=3D3787.57..3787.57 rows=3D24682 width=3D4) (actua= l > time=3D211.855..211.855 rows=3D0 loops=3D1) > -> Seq Scan on orders o (cost=3D0.00..3787.57 rows=3D2468= 2 > width=3D4) (actual time=3D0.047..147.170 rows=3D72121 loops=3D1) > Filter: (id_status > 3) > Total runtime: 1003.671 ms > > > I could use it in the following format, because I have to the moment only > the 4,6 values for the id_status. > > SELECT count(*) FROM orders o > INNER JOIN report r ON r.id_order=3Do.id > WHERE o.id_status IN (4,6) > > Explain analyze: > Aggregate (cost=3D5430.04..5430.04 rows=3D1 width=3D0) (actual > time=3D1472.877..1472.877 rows=3D1 loops=3D1) > -> Hash Join (cost=3D2108.22..5428.23 rows=3D720 width=3D0) (actual > time=3D342.080..1419.775 rows=3D72121 loops=3D1) > Hash Cond: ("outer".id_order =3D "inner".id) > -> Seq Scan on report r (cost=3D0.00..2952.21 rows=3D72121 widt= h=3D4) > (actual time=3D0.036..106.217 rows=3D72121 loops=3D1) > -> Hash (cost=3D2106.37..2106.37 rows=3D739 width=3D4) (actual > time=3D342.011..342.011 rows=3D0 loops=3D1) > -> Index Scan using orders_id_status_idx, > orders_id_status_idx on orders o (cost=3D0.00..2106.37 rows=3D739 width= =3D4) > (actual time=3D0.131..268.397 rows=3D72121 loops=3D1) > Index Cond: ((id_status =3D 4) OR (id_status =3D 6)) > Total runtime: 1474.356 ms > > How can I improve this query's performace?? The ideea is to count all the > values that I have in the database for the following conditions. If the > users puts in some other search fields on the where then the query runs > faster but in this format sometimes it takes a lot lot of time(sometimes > even 2,3 seconds). > > Can this be tuned somehow??? > > Regards, > Andy. > >