pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jim C. Nasby <jim@nasby.net>
To: pgsql-performance@postgresql.org
Subject: Why LIMIT after scanning the table?
Date: Wed, 30 Apr 2003 01:04:25 -0500
Message-ID: <20030430010425.T66185@flake.decibel.org> (raw)

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?"




view thread (4+ messages)  latest in thread

Message-ID: <20030430010425.T66185@flake.decibel.org>
Permalink:  ../20030430010425.T66185@flake.decibel.org/
Also on:    postgresql.org/message-id/20030430010425.T66185@flake.decibel.org

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: jim@nasby.net
  Subject: Re: Why LIMIT after scanning the table?
  In-Reply-To: <20030430010425.T66185@flake.decibel.org>
  References: <20030430010425.T66185@flake.decibel.org>

* 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