Subject: Two queries are better than one?
From: Karim Nassar <karim.nassar@acm.org>
Reply-To: karim.nassar@acm.org
To: pgsql-performance@postgresql.org
Content-Type: text/plain
Date: Thu, 28 Jul 2005 16:04:25 -0700
Message-Id: <1122591866.11860.11.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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>