X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id CF8BE4758E6 for ; Wed, 30 Apr 2003 12:22:13 -0400 (EDT) Received: from beamish.nsd.ca (beamish.nsd.ca [205.150.156.194]) by postgresql.org (Postfix) with ESMTP id EDEAB4758E6 for ; Wed, 30 Apr 2003 12:22:11 -0400 (EDT) Received: (from smap@localhost) by beamish.nsd.ca (8.9.3/8.9.3) id MAA11666; Wed, 30 Apr 2003 12:22:12 -0400 X-Authentication-Warning: beamish.nsd.ca: smap set sender to using -f Received: from reddog.nsd.ca(192.168.101.30) by beamish.nsd.ca via smap (V2.1/2.1+anti-relay+anti-spam) id xma011664; Wed, 30 Apr 03 12:22:05 -0400 Received: from nsd.ca (jllachan-linux.nsd.ca [192.168.101.148]) by reddog.nsd.ca (8.8.7/8.8.7) with ESMTP id MAA08705; Wed, 30 Apr 2003 12:15:58 -0400 Message-ID: <3EAFF86F.910A76EF@nsd.ca> Date: Wed, 30 Apr 2003 12:23:11 -0400 From: Jean-Luc Lachance X-Mailer: Mozilla 4.77 [en] (X11; U; Linux 2.4.18-24.7.x i686) X-Accept-Language: en MIME-Version: 1.0 To: jim@nasby.net Cc: pgsql-performance@postgresql.org Subject: Re: Why LIMIT after scanning the table? References: <20030430010425.T66185@flake.decibel.org> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Spam-Status: No, hits=-30.7 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,QUOTED_EMAIL_TEXT,REFERENCES, REPLY_WITH_QUOTES,X_AUTH_WARNING 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/265 X-Sequence-Number: 1771 If you only what to know if there is more than 100 rows, why not do: if exists ( SELECT 1 FROM email_rank WHERE project_id = :ProjectID OFFSET 100 LIMIT 1 ) "Jim C. Nasby" wrote: > > 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 () 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?" > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org