X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id C265147633A for ; Wed, 30 Apr 2003 10:22:23 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id AD81E4762FB for ; Wed, 30 Apr 2003 10:22:22 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h3UEMPU6021976; Wed, 30 Apr 2003 10:22:25 -0400 (EDT) To: jim@nasby.net Cc: pgsql-performance@postgresql.org Subject: Re: Why LIMIT after scanning the table? In-reply-to: <20030430010425.T66185@flake.decibel.org> References: <20030430010425.T66185@flake.decibel.org> Comments: In-reply-to "Jim C. Nasby" message dated "Wed, 30 Apr 2003 01:04:25 -0500" Date: Wed, 30 Apr 2003 10:22:25 -0400 Message-ID: <21975.1051712545@sss.pgh.pa.us> From: Tom Lane X-Spam-Status: No, hits=-32.3 required=5.0 tests=BAYES_00,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REFERENCES,REPLY_WITH_QUOTES autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/259 X-Sequence-Number: 1765 "Jim C. Nasby" writes: > explain select count(*) > FROM (SELECT * FROM email_rank WHERE project_id = :ProjectID LIMIT 100) AS t1; > The idea is that the inner-most query would only read the first 100 rows > it finds, then stop. Instead, if explain is to be believed (and speed > testing seems to indicate it's accurate), we'll read the entire table, > *then* pick the first 100 rows. Why is that? You're misreading the EXPLAIN output. Try EXPLAIN ANALYZE to see how many rows really get fetched. regards, tom lane