X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E617652825 for ; Fri, 29 Jul 2005 01:12:29 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 17890-01 for ; Fri, 29 Jul 2005 04:12:28 +0000 (GMT) Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.202]) by svr1.postgresql.org (Postfix) with ESMTP id A3D0C52816 for ; Fri, 29 Jul 2005 01:12:26 -0300 (ADT) Received: by zproxy.gmail.com with SMTP id z6so340693nzd for ; Thu, 28 Jul 2005 21:12:26 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:references; b=msI83EWGX0bxbMwMVT/loWZGAPMC1Dy8MfnswBjdR61ko5EDGBND1Jfst2aSiEtGaq1xeqzGhu9o6gqjpodhw8NzScR80oHQutMqkn4SrJHT47polFp98hZYgh2SkPC3om9WDiVSvaBy0qAR4Qrf1PTjdrhOoiwOgDFccvw9rsY= Received: by 10.36.252.6 with SMTP id z6mr1565962nzh; Thu, 28 Jul 2005 21:11:31 -0700 (PDT) Received: by 10.36.119.19 with HTTP; Thu, 28 Jul 2005 21:11:31 -0700 (PDT) Message-ID: Date: Fri, 29 Jul 2005 09:41:31 +0530 From: Gnanavel S Reply-To: Gnanavel S To: karim.nassar@acm.org Subject: Re: Two queries are better than one? Cc: pgsql-performance@postgresql.org In-Reply-To: <1122591866.11860.11.camel@localhost.localdomain> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_8088_21102920.1122610291112" References: <1122591866.11860.11.camel@localhost.localdomain> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.414 tagged_above=0 required=5 tests=AWL, HTML_40_50, HTML_MESSAGE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200507/457 X-Sequence-Number: 13698 ------=_Part_8088_21102920.1122610291112 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 7/29/05, Karim Nassar wrote: >=20 > I ran into a situation today maintaining someone else's code where the > sum time running 2 queries seems to be faster than 1. The original code > was split into two queries. I thought about joining them, but > considering the intelligence of my predecessor, I wanted to test it. >=20 > The question is, which technique is really faster? Is there some hidden > setup cost I don't see with explain analyze? >=20 > Postgres 7.4.7, Redhat AES 3 >=20 > Each query individually: >=20 > test=3D> explain analyze > test-> select * from order WHERE ord_batch=3D'343B' AND ord_id=3D'12-645'= ; > QUERY PLAN >=20 > -------------------------------------------------------------------------= --------------------------------------------------- > Index Scan using order_pkey on order (cost=3D0.00..6.02 rows=3D1 width=3D= 486)=20 > (actual time=3D0.063..0.066 rows=3D1 loops=3D1) > Index Cond: ((ord_batch =3D '343B'::bpchar) AND (ord_id =3D '12-645'::bpc= har)) > Total runtime: 0.172 ms > (3 rows) >=20 >=20 > test=3D> explain analyze > test-> select cli_name from client where cli_code=3D'1837'; > QUERY PLAN >=20 > -------------------------------------------------------------------------= -------------------------------------------- > Index Scan using client_pkey on client (cost=3D0.00..5.98 rows=3D2 width= =3D39)=20 > (actual time=3D0.043..0.047 rows=3D1 loops=3D1) > Index Cond: (cli_code =3D '1837'::bpchar) > Total runtime: 0.112 ms > (3 rows) >=20 > Joined: >=20 > test=3D> explain analyze > test-> SELECT cli_name,order.* > test-> FROM order > test-> JOIN client ON (ord_client =3D cli_code) > test-> WHERE ord_batch=3D'343B' AND ord_id=3D'12-645'; where is the cli_code condition in the above query? QUERY PLAN >=20 > -------------------------------------------------------------------------= --------------------------------------------------------- > Nested Loop (cost=3D0.00..12.00 rows=3D2 width=3D525) (actual time=3D0.12= 0..0.128rows=3D1 loops=3D1) > -> Index Scan using order_pkey on order (cost=3D0.00..6.02 rows=3D1 width= =3D486)=20 > (actual time=3D0.064..0.066 rows=3D1 loops=3D1) > Index Cond: ((ord_batch =3D '343B'::bpchar) AND (ord_id =3D '12-645'::bpc= har)) > -> Index Scan using client_pkey on client (cost=3D0.00..5.98 rows=3D1=20 > width=3D51) (actual time=3D0.023..0.026 rows=3D1 loops=3D1) > Index Cond: ("outer".ord_client =3D client.cli_code) > Total runtime: 0.328 ms > (6 rows) >=20 >=20 > -- > Karim Nassar >=20 >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend >=20 --=20 with regards, S.Gnanavel Satyam Computer Services Ltd. ------=_Part_8088_21102920.1122610291112 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline

On 7/29/05, Karim Nassar <karim.= nassar@acm.org> wrote:
I ran into a situation today maintaining someone else's code where the
s= um time running 2 queries seems to be faster than 1. The original code
w= as split into two queries. I thought about joining them, but
considering= the intelligence of my predecessor, I wanted to test it.

The question is, which technique is really faster? Is there some hi= dden
setup cost I don't see with explain analyze?

Postgres 7.4.7,= Redhat AES 3

Each query individually:

test=3D> explain an= alyze
test-> select * from order  WHERE ord_batch=3D'343B' AND o= rd_id=3D'12-645';
         =             &nb= sp;            =             &nb= sp;          QUERY PLAN
-------------------------------------------------------------= ---------------------------------------------------------------
Index S= can using order_pkey on order  (cost=3D0.00..6.02 rows=3D1 width= =3D486) (actual time=3D 0.063..0.066 rows=3D1 loops=3D1)
   Index Cond: ((ord_batch = =3D '343B'::bpchar) AND (ord_id =3D '12-645'::bpchar))
Total runtime: 0= .172 ms
(3 rows)


test=3D> explain analyze
test->&nbs= p;    select cli_name from client where cli_code=3D'1837';
            = ;            &n= bsp;            = ;            &n= bsp;   QUERY PLAN
-------------------------------------------------------------= --------------------------------------------------------
Index Scan usi= ng client_pkey on client  (cost=3D0.00..5.98 rows=3D2 width=3D39)= (actual time=3D 0.043..0.047 rows=3D1 loops=3D1)
   Index Cond: (cli_code =3D = '1837'::bpchar)
Total runtime: 0.112 ms
(3 rows)

Joined:
<= br>test=3D> explain analyze
test->    SELECT c= li_name,order.*
test->        = ;       FROM order
test->          &n= bsp;    JOIN client ON (ord_client =3D cli_code)
test->   &nbs= p;          WHERE ord_batch=3D'343B' AND ord_id=3D'12-645';

where is the cli_code condition in the above query?
 

&= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;         QUERY PLAN
-------------------------------------------------------------------= ---------------------------------------------------------------
Nested = Loop  (cost=3D0.00..12.00 rows=3D2 width=3D525) (actual time=3D0.= 120..0.128 rows=3D1 loops=3D1)
   ->  Index Scan using order_pkey on order  (cost=3D0.00..6.02 rows=3D1 width=3D486) (actual time=3D0.064..0.066 rows=3D1 loops=3D1)
     &n= bsp;   Index Cond: ((ord_batch =3D '343B'::bpchar) AND (ord_id = =3D '12-645'::bpchar))
   ->  Index Scan using client_pkey on client  (cost=3D0.00..5.98 rows=3D1 width=3D51) (actual time=3D0.023..0.026 rows=3D1 loops=3D1)
     &n= bsp;   Index Cond: ("outer".ord_client =3D client.cli_c= ode)
Total runtime: 0.328 ms
(6 rows)


--
Karim Nassar = <karim.nassar@acm.org >


---------------------------(end of broadcast)----------= -----------------
TIP 6: explain analyze is your friend
=



--
with regards,
S.Gnanavel
Satya= m Computer Services Ltd. ------=_Part_8088_21102920.1122610291112--