From: Gnanavel S <s.gnanavel@gmail.com>
To: karim.nassar@acm.org
Cc: pgsql-performance@postgresql.org
Subject: Re: Two queries are better than one?
Date: Fri, 29 Jul 2005 09:41:31 +0530
Message-ID: <eec3b03c050728211116bafdab@mail.gmail.com> (raw)
In-Reply-To: <1122591866.11860.11.camel@localhost.localdomain>
References: <1122591866.11860.11.camel@localhost.localdomain>
On 7/29/05, Karim Nassar <karim.nassar@acm.org> wrote:
>
> 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';
where is the cli_code condition in the above query?
QUERY PLAN
>
> ----------------------------------------------------------------------------------------------------------------------------------
> Nested Loop (cost=0.00..12.00 rows=2 width=525) (actual time=0.120..0.128rows=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>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
--
with regards,
S.Gnanavel
Satyam Computer Services Ltd.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: s.gnanavel@gmail.com, karim.nassar@acm.org
Subject: Re: Two queries are better than one?
In-Reply-To: <eec3b03c050728211116bafdab@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox