On Mon, May 27, 2013= at 10:59 AM, Evgeny Shishkin <itparanoia@gmail.com> wrote:=

<= div class=3D"im">
On May 27, 2013, at 6:35 PM, John Mudd <johnbmudd@gmail.com> wr= ote:

Thanks, that's easy enough to test. Didn't see= m to help though.

Ok. And if you CLUSTE= R tables USING PK?

test=3D= # REINDEX index test_select_pkey;
REINDEX

test=3D# VACUUM ANALYZE test_select ;
VACUUM

(stopped postgres; reset O/S cache; started postgres)

test=3D# explain analyze SELECT * FROM test_select= WHERE key1 >=3D 500000 ORDER BY key1, key2, key3, id LIMIT 1;
= = = ; QUERY PLAN &= nbsp; &= nbsp; &= nbsp;

-----------------------------------------------------------------------= ---------------------------------------------------------------
<= div> Limit (cost=3D0.00..0.08 rows=3D1 width=3D21) (actual time=3D= 16.368..16.369 rows=3D1 loops=3D1)

-> Index Scan using my_key on test_select (= cost=3D0.00..41981.16 rows=3D501333 width=3D21) (actual time=3D16.366..16.36= 6 rows=3D1 loops=3D1)
Index Cond= : (key1 >=3D 500000)
Total runtime: 16.444 ms

(stopped postgres; reset O/S cache; started postgres= )

test=3D# explain analyze SELECT * FROM test_s= elect WHERE id =3D 500000;

&= nbsp; &= nbsp; QUERY PLAN
----------------------------------------------------------------------= -----------------------------------------------------------

Index Scan u= sing test_select_pkey on test_select (cost=3D0.00..8.36 rows=3D1 width= =3D21) (actual time=3D23.072..23.074 rows=3D1 loops=3D1)
Index Cond: (id =3D 500000)
Total runtime: 23.192 ms

On Mon, May 27, 2013 at 10:21 AM, Evgeny Shishkin <itparanoia@gmail.com= > wrote:

On May 27, 2013, at 6:02 PM, 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 th= at includes an ORDER BY? I was really hoping using the primary key would giv= e me a boost.
>

You created my_key after data loading, and PK was there all the time.<= br> If you REINDEX PK, i bet it will be as fast.

> I stopped the server and cleared the O/S cache using "sync; echo 3 >= /proc/sys/vm/drop_caches" between the runs.
>
>
>
> test=3D# VACUUM ANALYZE test_select;
> VACUUM
>
> (stopped postgres; reset O/S cache; started postgres)
>
> test=3D# explain analyze SELECT * FROM test_select WHERE key1 >=3D 5= 00000 ORDER BY key1, key2, key3, id LIMIT 1;
> &= nbsp; &= nbsp; QUERY P= LAN
> -----------------------------------------------------------------------= ---------------------------------------------------------------
> Limit (cost=3D0.00..0.08 rows=3D1 width=3D21) (actual time=3D= 12.599..12.600 rows=3D1 loops=3D1)
> -> Index Scan using my_key on test_select (= cost=3D0.00..41895.49 rows=3D498724 width=3D21) (actual time=3D12.597..12.59= 7 rows=3D1 loops=3D1)
> Index Cond: (key1 >=3D 500000)
= > Total runtime: 12.678 ms
>
> (stopped postgres; reset O/S cache; started postgres)
>
> test=3D# explain analyze SELECT * FROM test_select WHERE id =3D 500000;=
> &= nbsp; &= nbsp; QUERY PLAN
> -----------------------------------------------------------------------= ----------------------------------------------------------
> Index Scan using test_select_pkey on test_select (cost=3D0.= 00..8.36 rows=3D1 width=3D21) (actual time=3D31.396..31.398 rows=3D1 loops=3D= 1)
> Index Cond: (id =3D 500000)
> Total runtime: 31.504 ms
>
>
>
> Schema:
>
> test=3D# \d test_select
> &= nbsp; Table "public.test_select"
> Column | Type | &= nbsp; Modifiers
> --------+--------------+-----------------------------------------------= -----------
> id | integer | not null default= nextval('test_select_id_seq'::regclass)
> key1 | integer |
> key2 | integer |
> key3 | integer |
> data | character(4) |
> Indexes:
> "test_select_pkey" PRIMARY KEY, btree (id)
> "my_key" btree (key1, key2, key3, id)
>
> test=3D#
>
>
>
> Sample data:
>
> test=3D# SELECT * FROM test_select LIMIT 10;
> id | key1 | key2 | key3 | dat= a
> ----+--------+--------+--------+------
> 1 | 984966 | 283954 | 772063 | x
> 2 | 817668 | 393533 | 924888 | x
> 3 | 751039 | 798753 | 454309 | x
> 4 | 128505 | 329643 | 280553 | x
> 5 | 105600 | 257225 | 710015 | x
> 6 | 323891 | 615614 | 83206 | x
> 7 | 194054 | 63506 | 353171 | x
> 8 | 212068 | 881225 | 271804 | x
> 9 | 644180 | 26693 | 200738 | x
> 10 | 136586 | 498699 | 554417 | x
> (10 rows)
>
>
>
>
> Here's how I populated the table:
>
> import psycopg2
>
> conn =3D psycopg2.connect('dbname=3Dtest')
>
> cur =3D conn.cursor()
>
> def random_int():
> n =3D 1000000
> return random.randint(0,n)
>
> def random_key():
> return random_int(), random_int(), random_int()
>
> def create_table():
> cur.execute('''
> DROP TABLE IF EXISTS test_sel= ect;
>
> CREATE TABLE test_select ( > id = ; SERIAL PRIMA= RY KEY,
> key1 &nb= sp; INTEGER,
> key2 &nb= sp; INTEGER,
> key3 &nb= sp; INTEGER,
> data &nb= sp; char(4)
> );
> ''')
> conn.commit()
>
> n =3D 1000000
> for i in range(n):
> cur.execute("INSERT INTO test_select(key1, k= ey2, key3, data) VALUES(%s, %s, %s, 'x')", random_key())
> conn.commit()
>
> cur.execute('CREATE INDEX my_key ON test_select(key1, key= 2, key3, id)')
> conn.commit()
>
> create_table()
>