Message-ID: <42E98DDD.5000505@arbash-meinel.com>
Date: Thu, 28 Jul 2005 21:01:01 -0500
From: John A Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0.6 (Macintosh/20050716)
MIME-Version: 1.0
To: karim.nassar@acm.org,
	Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: Two queries are better than one?
References: <1122591866.11860.11.camel@localhost.localdomain>
In-Reply-To: <1122591866.11860.11.camel@localhost.localdomain>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enigAB99DF19056F44E02782CD13"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enigAB99DF19056F44E02782CD13
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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)
>
>


--------------enigAB99DF19056F44E02782CD13
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (Darwin)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFC6Y3eJdeBCYSNAAMRAvTBAKDC2CENfXNdYiCbQ5RjQ/M4tlnIOQCfVFaK
CSN0dsCFMpgB4e/eXMTqFmA=
=Nvyw
-----END PGP SIGNATURE-----

--------------enigAB99DF19056F44E02782CD13--