Skip site navigation (1) Skip section navigation (2)

Re: Avoiding a seq scan on a table.

From: LWATCDR <lwatcdr(at)gmail(dot)com>
To: "Daniel T(dot) Staal" <DStaal(at)usa(dot)net>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Avoiding a seq scan on a table.
Date: 2008-01-14 17:35:30
Message-ID: 8c38cea40801140935y4e39a510vff58bde219693356@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
that is very odd since that table has 141 records in it.

here is a different query that I ran.
SELECT COUNT(*) FROM rma where terminatedate is NULL;
This returns a value of 254 for the count but this is what I get from explain.

Aggregate  (cost=219.77..219.78 rows=1 width=0)
  ->  Seq Scan on rma  (cost=0.00..219.11 rows=264 width=0)
        Filter: (terminatedate IS NULL)
This says that rows =1 but returns 254 rows of data?
The table contains over 7000 rows.


On Jan 14, 2008 12:22 PM, Daniel T. Staal <DStaal(at)usa(dot)net> wrote:
>
> On Mon, January 14, 2008 12:14 pm, LWATCDR wrote:
> > Really? From what I have done in writing my own code I have found
> > hashing to be faster than a btree but then when I wrote my own hashing
> > it was a specific type of key.
> > Anyway I put in the tree indexes and I am still getting a seq scan.
> >
> > Aggregate  (cost=12.12..12.13 rows=1 width=0)
> >   ->  Result  (cost=0.00..12.12 rows=1 width=0)
> >         One-Time Filter: NULL::boolean
> >         ->  Seq Scan on issuetracking  (cost=0.00..12.12 rows=1 width=0)
> >               Filter: (((issue_target)::text = 'david'::text) OR
> > ((manager)::text = 'david'::text))
>
> Based on that cost, a sequence scan is probably the fastest yet: It's such
> a small dataset that fetching the index and working with it before going
> back and fetching the data is just overkill.
>
> When you add a few dozen more rows or so, it'll switch to using the index.
>
> Daniel T. Staal
>
> ---------------------------------------------------------------
> This email copyright the author.  Unless otherwise noted, you
> are expressly allowed to retransmit, quote, or otherwise use
> the contents for non-commercial purposes.  This copyright will
> expire 5 years after the author's death, or in 30 years,
> whichever is longer, unless such a period is in excess of
> local copyright law.
> ---------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>

In response to

Responses

pgsql-novice by date

Next:From: Daniel T. StaalDate: 2008-01-14 17:55:58
Subject: Re: Avoiding a seq scan on a table.
Previous:From: Alan HodgsonDate: 2008-01-14 17:30:30
Subject: Re: Avoiding a seq scan on a table.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group