From: John A Meinel <john@arbash-meinel.com>
To: karim.nassar@acm.org, Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: Two queries are better than one?
Date: Thu, 28 Jul 2005 21:01:01 -0500
Message-ID: <42E98DDD.5000505@arbash-meinel.com> (raw)
In-Reply-To: <1122591866.11860.11.camel@localhost.localdomain>
References: <1122591866.11860.11.camel@localhost.localdomain>
Karim Nassar 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?
Yes, the time it takes your user code to parse the result, and create
the new query. :)
It does seem like you are taking an extra 0.1ms for the combined query,
but that means you don't have another round trip to the database. So
that would mean one less context switch, and you don't need to know what
the cli_code is before you can get the cli_name.
I would guess the overhead is the time for postgres to parse out the
text, place another index query, and then combine the rows. It seems
like this shouldn't take 0.1ms, but then again, that isn't very long.
Also, did you run it *lots* of times to make sure that this isn't just
noise?
John
=:->
>
> 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)
>
>
Attachments:
signature.asc (application/pgp-signature, 252B) download
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: john@arbash-meinel.com
Subject: Re: Two queries are better than one?
In-Reply-To: <42E98DDD.5000505@arbash-meinel.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