Message-ID:

"Confound these ancestors.... They've stolen our best ideas!" -- Ben Jonson

devel / comp.lang.python / Re: Postgresql equivalent of Python's timeit?

Re: Postgresql equivalent of Python's timeit?

<mailman.314.1694968465.23016.python-list@python.org>

https://www.novabbs.com/devel/article-flat.php?id=24009&group=comp.lang.python#24009

copy link Newsgroups: comp.lang.python

Path: i2pn2.org!i2pn.org!weretis.net!feeder8.news.weretis.net!fu-berlin.de!uni-berlin.de!not-for-mail
From: hjp-pyt...@hjp.at (Peter J. Holzer)
Newsgroups: comp.lang.python
Subject: Re: Postgresql equivalent of Python's timeit?
Date: Sun, 17 Sep 2023 18:34:17 +0200
Lines: 83
Message-ID: <mailman.314.1694968465.23016.python-list@python.org>
References: <20230915174516.2uliozlczlu7dxcq@hjp.at>
<DB9PR10MB6689F40595F9ED0C07F46B1283F4A@DB9PR10MB6689.EURPRD10.PROD.OUTLOOK.COM>
<20230917163417.fo3ss5rq5a3b7phy@hjp.at>
Mime-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
protocol="application/pgp-signature"; boundary="ikk6mu5g4y7pgk2y"
X-Trace: news.uni-berlin.de 6jIsa+cz84Kaht9gfZb2FAXJhal/Ech4yC4I7GRCrYkw==
Cancel-Lock: sha1:y7BfjwT8l50B3loFlKyuLXW9g4g= sha256:vJpBhxmrdJYRJj9wDrOzXd99BKHn9A+Rol5dZF+6Yu4=
Return-Path: <hjp-python@hjp.at>
X-Original-To: python-list@python.org
Delivered-To: python-list@mail.python.org
Authentication-Results: mail.python.org; dkim=none reason="no signature";
dkim-adsp=none (unprotected policy); dkim-atps=neutral
X-Spam-Status: OK 0.001
X-Spam-Evidence: '*H*': 1.00; '*S*': 0.00; 'python?': 0.03; '(which':
0.04; 'content-type:multipart/signed': 0.05; 'row': 0.05; '2023':
0.07; 'hopefully': 0.07; 'compute': 0.09; 'content-
type:application/pgp-signature': 0.09; 'filename:fname piece:asc':
0.09; 'filename:fname piece:signature': 0.09;
'filename:fname:signature.asc': 0.09; 'subject:Python': 0.12;
'"creative': 0.16; '(unless': 0.16; '+0200,': 0.16; '=====': 0.16;
'__/': 0.16; 'bulk': 0.16; 'caching': 0.16; 'challenge!"': 0.16;
'connection.': 0.16; 'from:addr:hjp-python': 0.16;
'from:addr:hjp.at': 0.16; 'from:name:peter j. holzer': 0.16;
'hjp@hjp.at': 0.16; 'holzer': 0.16; 'ok.': 0.16; 'reality.': 0.16;
'separately': 0.16; 'slow': 0.16; 'stross,': 0.16; 'times,': 0.16;
'url-ip:212.17.106/24': 0.16; 'url-ip:212.17/16': 0.16; 'url:hjp':
0.16; 'worst-case': 0.16; '|_|_)': 0.16; 'wrote:': 0.16; 'python':
0.16; 'probably': 0.17; "can't": 0.17; 'to:addr:python-list':
0.20; 'issue': 0.21; 'python,': 0.25; 'bit': 0.27; 'function':
0.27; 'sense': 0.28; 'whole': 0.30; 'think': 0.32; 'good.': 0.32;
'python-list': 0.32; 'sep': 0.32; "wouldn't": 0.32; 'but': 0.32;
"i'll": 0.33; 'there': 0.33; 'same': 0.34; 'header:In-Reply-To:1':
0.34; 'possibly': 0.36; 'thanks,': 0.36; 'change': 0.36; 'could':
0.38; 'means': 0.38; 'quite': 0.39; 'shared': 0.39; 'explain':
0.40; 'want': 0.40; 'likely': 0.61; "there's": 0.61; 'gives':
0.62; 'received:212': 0.62; 'simply': 0.63; 'between': 0.63;
'times.': 0.64; 'opportunity': 0.66; 'received:userid': 0.66;
'operations': 0.68; 'during': 0.69; 'url-ip:212/8': 0.69; 'times':
0.69; 'depending': 0.70; 'establish': 0.70; 'charset:iso-8859-1':
0.73; 'analyze': 0.75; 'database': 0.80; 'queries': 0.81;
'postgresql': 0.84; 'received:at': 0.84; 'rid': 0.84; 'shot.':
0.84; 'dropping': 0.91; 'variation': 0.91
Mail-Followup-To: python-list@python.org
Content-Disposition: inline
In-Reply-To: <DB9PR10MB6689F40595F9ED0C07F46B1283F4A@DB9PR10MB6689.EURPRD10.PROD.OUTLOOK.COM>
X-BeenThere: python-list@python.org
X-Mailman-Version: 2.1.39
Precedence: list
List-Id: General discussion list for the Python programming language
<python-list.python.org>
List-Unsubscribe: <https://mail.python.org/mailman/options/python-list>,
<mailto:python-list-request@python.org?subject=unsubscribe>
List-Archive: <https://mail.python.org/pipermail/python-list/>
List-Post: <mailto:python-list@python.org>
List-Help: <mailto:python-list-request@python.org?subject=help>
List-Subscribe: <https://mail.python.org/mailman/listinfo/python-list>,
<mailto:python-list-request@python.org?subject=subscribe>
X-Mailman-Original-Message-ID: <20230917163417.fo3ss5rq5a3b7phy@hjp.at>
X-Mailman-Original-References: <20230915174516.2uliozlczlu7dxcq@hjp.at>
<DB9PR10MB6689F40595F9ED0C07F46B1283F4A@DB9PR10MB6689.EURPRD10.PROD.OUTLOOK.COM>

by: Peter J. Holzer - Sun, 17 Sep 2023 16:34 UTC

Attachments: signature.asc (application/pgp-signature)

On 2023-09-17 11:01:43 +0200, Albert-Jan Roskam via Python-list wrote:
> On Sep 15, 2023 19:45, "Peter J. Holzer via Python-list"
> <python-list@python.org> wrote:
>
> On 2023-09-15 17:42:06 +0200, Albert-Jan Roskam via Python-list wrote:
> >    This is more related to Postgresql than to Python, I hope this is
> ok.
> >    I want to measure Postgres queries N times, much like Python timeit
> >    (https://docs.python.org/3/library/timeit.html). I know about
> EXPLAIN
> >    ANALYZE and psql \timing, but there's quite a bit of variation in
> the
> >    times. Is there a timeit-like function in Postgresql?
>
> Why not simply call it n times from Python?
>
> (But be aware that calling the same query n times in a row is likely to
> be
> unrealistically fast because most of the data will already be in
> memory.)
>
> ====> Thanks, I'll give this a shot. Hopefully the caching is not an issue if I
> don't re-use the same database connection.

There is some per-session caching, but the bulk of it is shared between
sessions or even in the operating system. And you wouldn't want to get
rid of these caches either (which you could do by rebooting or - a bit
faster - restarting postgres and dropping the caches
(/proc/sys/vm/drop_caches on Linux), because that would make the
benchmark unrealistically slow (unless you want to establish some
worst-case baseline). During normal operations some data will be cached,
but probably not all of it and it will change depending on workload and
possibly other factors.

I think Avi's advice to wait for a few minutes between repetitions is
good. Of course that means that you can't just time the whole thing but
have to time each query separately and then compute the average. (On the
bright side that also gives you the opportunity to compute standard
deviation, min, max, quantiles, etc.)

Attachments: signature.asc (application/pgp-signature)

Subject	Author
Re: Postgresql equivalent of Python's timeit?	Peter J. Holzer