X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id EC7A8476300 for ; Wed, 30 Apr 2003 02:04:25 -0400 (EDT) Received: from flake.decibel.org (flake.decibel.org [66.93.216.19]) by postgresql.org (Postfix) with SMTP id 18C12475458 for ; Wed, 30 Apr 2003 02:04:25 -0400 (EDT) Received: (qmail 86612 invoked by uid 1001); 30 Apr 2003 06:04:25 -0000 Date: Wed, 30 Apr 2003 01:04:25 -0500 From: "Jim C. Nasby" To: pgsql-performance@postgresql.org Subject: Why LIMIT after scanning the table? Message-ID: <20030430010425.T66185@flake.decibel.org> Reply-To: jim@nasby.net Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5.1i X-Operating-System: FreeBSD 4.8-RELEASE i386 X-Distributed: Join the Effort! http://www.distributed.net X-Spam-Status: No, hits=-13.5 required=5.0 tests=BAYES_01,SIGNATURE_SHORT_SPARSE,USER_AGENT_MUTT 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/254 X-Sequence-Number: 1760 I'm doing something where I just need to know if we have more than 100 rows in a table. Not wanting to scan the whole table, I thought I'd get cute... explain select count(*) FROM (SELECT * FROM email_rank WHERE project_id = :ProjectID LIMIT 100) AS t1; QUERY PLAN ------------------------------------------------------------------------------------- Aggregate (cost=111.32..111.32 rows=1 width=48) -> Subquery Scan t1 (cost=0.00..111.07 rows=100 width=48) -> Limit (cost=0.00..111.07 rows=100 width=48) -> Seq Scan on email_rank (cost=0.00..76017.40 rows=68439 width=48) Filter: (project_id = 24) 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? FYI... Table "public.email_rank" Column | Type | Modifiers -----------------------+---------+-------------------- project_id | integer | not null id | integer | not null first_date | date | not null last_date | date | not null day_rank | integer | not null default 0 day_rank_previous | integer | not null default 0 overall_rank | integer | not null default 0 overall_rank_previous | integer | not null default 0 work_today | bigint | not null default 0 work_total | bigint | not null default 0 Indexes: email_rank_pkey primary key btree (project_id, id), email_rank__day_rank btree (project_id, day_rank), email_rank__overall_rank btree (project_id, overall_rank) -- Jim C. Nasby (aka Decibel!) jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"