I flushed the caches in an attempt to get meaningful results. I've seen complaints to previous posts that don't include clearing the caches.
I agree this tends to be artificial in another direction. I will strive to come up with a more realistic test environment next time. Maybe performing many random reads initially to fill the caches with random blocks. That might allow for minimal assistance from the cache and be more realistic.
On Thu, May 30, 2013 at 11:13 AM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote:
On Mon, May 27, 2013 at 9:02 AM, John Mudd <johnbmudd@xxxxxxxxx> wrote:why are you flushing postgres/os cache? when you do that, you are
> Postgres 9.1.2 on Ubuntu 12.04
>
> Any reason why a select by primary key would be slower than a select that
> includes an ORDER BY? I was really hoping using the primary key would give
> me a boost.
>
> I stopped the server and cleared the O/S cache using "sync; echo 3 >
> /proc/sys/vm/drop_caches" between the runs.
>
>
>
> test=# VACUUM ANALYZE test_select;
> VACUUM
>
> (stopped postgres; reset O/S cache; started postgres)
>
> test=# explain analyze SELECT * FROM test_select WHERE key1 >= 500000 ORDER
> BY key1, key2, key3, id LIMIT 1;
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=0.00..0.08 rows=1 width=21) (actual time=12.599..12.600 rows=1
> loops=1)
> -> Index Scan using my_key on test_select (cost=0.00..41895.49
> rows=498724 width=21) (actual time=12.597..12.597 rows=1 loops=1)
> Index Cond: (key1 >= 500000)
> Total runtime: 12.678 ms
>
> (stopped postgres; reset O/S cache; started postgres)
measuring raw read time from disks. Typical disk seek time is
measured in milliseconds so the timings are completely appropriate
once you remove caching effects. Hard drives (at least, the spinning
kind) are slow and one of the major challenges of database and
hardware engineering is working around their limitations. Fortunately
it looks like faster storage will soon be commonplace for reasonable
prices.
merlin
