From: Tom Lane <tgl@sss.pgh.pa.us>
To: Greg Stark <gsstark@mit.edu>
Cc: Neil Conway <neilc@samurai.com>
Cc: Dror Matalon <dror@zapatec.com>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Various performance questions
Date: Mon, 27 Oct 2003 14:26:07 -0500
Message-ID: <2167.1067282767@sss.pgh.pa.us> (raw)
In-Reply-To: <87n0bmwl7w.fsf@stark.dyndns.tv>
References: <20031026194449.GD2979@rlx11.zapatec.com>
<87u15vxrue.fsf@stark.dyndns.tv>
<1067269309.459.33.camel@tokyo>
<877k2qy36r.fsf@stark.dyndns.tv>
<1067280005.463.48.camel@tokyo>
<1735.1067280732@sss.pgh.pa.us>
<87n0bmwl7w.fsf@stark.dyndns.tv>
Greg Stark <gsstark@mit.edu> writes:
> I'm still puzzled why the times on these are so different when the latter
> returns fewer records and both are doing sequential scans:
My best guess is that it's simply the per-tuple overhead of cycling
tuples through the two plan nodes. When you have no actual I/O happening,
the seqscan runtime is going to be all CPU time, something of the form
cost_per_page * number_of_pages_processed +
cost_per_tuple_scanned * number_of_tuples_scanned +
cost_per_tuple_returned * number_of_tuples_returned
I don't have numbers for the relative sizes of those three costs, but
I doubt that any of them are negligible compared to the other two.
Adding a WHERE clause increases cost_per_tuple_scanned but reduces the
number_of_tuples_returned, and so it cuts the contribution from the
third term, evidently by more than the WHERE clause adds to the second
term.
Ny own profiling had suggested that the cost-per-tuple-scanned in the
aggregate node dominated the seqscan CPU costs, but that might be
platform-specific, or possibly have something to do with the fact that
I was profiling an assert-enabled build.
It might be worth pointing out that EXPLAIN ANALYZE adds two kernel
calls (gettimeofday or some such) into each cycle of the plan nodes;
that's probably inflating the cost_per_tuple_returned by a noticeable
amount.
regards, tom lane
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: tgl@sss.pgh.pa.us, gsstark@mit.edu, neilc@samurai.com, dror@zapatec.com
Subject: Re: Various performance questions
In-Reply-To: <2167.1067282767@sss.pgh.pa.us>
* 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