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

Re: Using "LIMIT" is much faster even though, searching

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Cc: "Hyun-Sung, Jang" <siche(at)siche(dot)net>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: Using "LIMIT" is much faster even though, searching
Date: 2004-12-01 20:25:42
Message-ID: 200412011225.42119.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hyun-Sung,

> do you need all of verbose information??
> VACUUM FULL ANALYZE VERBOSE give me a lot of infomation,
> so i just cut zipcode parts.

Oh, sorry.  I meant just "VACUUM FULL ANALYZE VERBOSE zipcode", not the whole 
database.   Should have been clearer.

> ==start====================================================================
>=========== INFO:  vacuuming "public.zipcode"
> INFO:  "zipcode": found 0 removable, 47705 nonremovable row versions in
> 572 pages
> DETAIL:  0 dead row versions cannot be removed yet.

OK, looks like you're clean.

> I just choose zipcode table for this test.
> not only zipcode table but other table also give me same result.
>
> SELECT * FROM table_name WHERE PK = 'xxx'
>
> was always slower than
>
> SELECT * FROM table_name WHERE PK = 'xxx' LIMIT 1
>
> when sequence scan .

yeah?  So?  Stop using sequence scan!  You've just demonstrated that, if you 
don't force the planner to use sequence scan, things run at the same speed 
with or without the LIMIT.  So you're causing a problem by forcing the 
planner into a bad plan.

See Andrew's explanation of why it works this way.

> ah, why i'm using sequence as PK instead of zip code is
> in korea, the small towns doesn't have it's own zipcode
> so they share other big city's.
> that's why zip code can't be a primary key.
> actually, i'm not using sequence to find zipcode.
> i made it temporary for this test.

That makes sense.

--Josh

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

In response to

pgsql-performance by date

Next:From: Brian HirtDate: 2004-12-01 22:09:04
Subject: Re: query with timestamp not using index
Previous:From: Josh BerkusDate: 2004-12-01 20:19:00
Subject: Re: [PERFORM] pg_restore taking 4 hours!

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