Two queries are better than one? - Mailing list pgsql-performance

From Karim Nassar
Subject Two queries are better than one?
Date
Msg-id 1122591866.11860.11.camel@localhost.localdomain
Whole thread Raw
Responses Re: Two queries are better than one?  (Michael Fuhr <mike@fuhr.org>)
Re: Two queries are better than one?  (John A Meinel <john@arbash-meinel.com>)
Re: Two queries are better than one?  (Gnanavel S <s.gnanavel@gmail.com>)
List pgsql-performance
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.

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

Postgres 7.4.7, Redhat AES 3

Each query individually:

test=> explain analyze
test-> select * from order  WHERE ord_batch='343B' AND ord_id='12-645';
                                                         QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------
 Index Scan using order_pkey on order  (cost=0.00..6.02 rows=1 width=486) (actual time=0.063..0.066 rows=1 loops=1)
   Index Cond: ((ord_batch = '343B'::bpchar) AND (ord_id = '12-645'::bpchar))
 Total runtime: 0.172 ms
(3 rows)


test=> explain analyze
test->     select cli_name from client where cli_code='1837';
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Index Scan using client_pkey on client  (cost=0.00..5.98 rows=2 width=39) (actual time=0.043..0.047 rows=1 loops=1)
   Index Cond: (cli_code = '1837'::bpchar)
 Total runtime: 0.112 ms
(3 rows)

Joined:

test=> explain analyze
test->    SELECT cli_name,order.*
test->               FROM order
test->               JOIN client ON (ord_client = cli_code)
test->              WHERE ord_batch='343B' AND ord_id='12-645';
                                                            QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..12.00 rows=2 width=525) (actual time=0.120..0.128 rows=1 loops=1)
   ->  Index Scan using order_pkey on order  (cost=0.00..6.02 rows=1 width=486) (actual time=0.064..0.066 rows=1
loops=1)
         Index Cond: ((ord_batch = '343B'::bpchar) AND (ord_id = '12-645'::bpchar))
   ->  Index Scan using client_pkey on client  (cost=0.00..5.98 rows=1 width=51) (actual time=0.023..0.026 rows=1
loops=1)
         Index Cond: ("outer".ord_client = client.cli_code)
 Total runtime: 0.328 ms
(6 rows)


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


pgsql-performance by date:

Previous
From: "Luke Lonergan"
Date:
Subject: Re: Finding bottleneck
Next
From: Mark Wong
Date:
Subject: Re: [PATCHES] COPY FROM performance improvements