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

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