From: | Karim Nassar <karim(dot)nassar(at)acm(dot)org> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Two queries are better than one? |
Date: | 2005-07-28 23:04:25 |
Message-ID: | 1122591866.11860.11.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(dot)nassar(at)acm(dot)org>
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Wong | 2005-07-28 23:43:52 | Re: [PATCHES] COPY FROM performance improvements |
Previous Message | Josh Berkus | 2005-07-28 21:57:10 | Re: wal_buffer tests in |