X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id AB983D1B550 for ; Mon, 27 Oct 2003 19:26:59 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 15548-02 for ; Mon, 27 Oct 2003 15:26:29 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 1F875D1B4EB for ; Mon, 27 Oct 2003 15:26:07 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id h9RJQ7it002168; Mon, 27 Oct 2003 14:26:08 -0500 (EST) To: Greg Stark Cc: Neil Conway , Dror Matalon , PostgreSQL Performance Subject: Re: Various performance questions 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> Comments: In-reply-to Greg Stark message dated "27 Oct 2003 14:10:11 -0500" Date: Mon, 27 Oct 2003 14:26:07 -0500 Message-ID: <2167.1067282767@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/693 X-Sequence-Number: 4441 Greg Stark 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