Re: Slow SELECT by primary key? Postgres 9.1.2

On Thu, May 30, 2013 at 10:22 AM, Igor Neyman <ineyman@perceptron.com> wrote:
>> -----Original Message-----
>> From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-
>> owner@postgresql.org] On Behalf Of Merlin Moncure
>> Sent: Thursday, May 30, 2013 11:14 AM
>> To: John Mudd
>> Cc: pgsql-performance@postgresql.org
>> Subject: Re: [PERFORM] Slow SELECT by primary key? Postgres 9.1.2
>>
>> On Mon, May 27, 2013 at 9:02 AM, John Mudd <johnbmudd@gmail.com> wrote:
>> > 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)
>>
>>
>> why are you flushing postgres/os cache?  when you do that, you are
>> 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
>>
>
> True.
> But, on the hand (back to original question),
> execution plans that John got before and after suggested change in configuration parameters are exactly the same,
thoughtiming is different but only due to buffer cache issue. 

Right.  Well, I think Matheus's answer is the right one.   But my
point was that what's going here is we are measuring number of raw
uncached seeks to satisfy query on index A vs B.  Pure luck in terms
of how the index data is organized could throw it off one way or the
other.  But the test methodology is bogus because the root index pages
will stay hot so the more compact pkey will likely be slightly faster
in real world usage.  (but, I prefer the composite key style of design
especially for range searching).

merlin