Re: Slow SELECT by primary key? Postgres 9.1.2

On May 27, 2013, at 6:35 PM, John Mudd <johnbmudd(at)gmail(dot)com> wrote:

> Thanks, that's easy enough to test. Didn't seem to help though.
>

Ok. And if you CLUSTER tables USING PK?

>
> test=# REINDEX index test_select_pkey;
> REINDEX
> 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=16.368..16.369 rows=1 loops=1)
> -> Index Scan using my_key on test_select (cost=0.00..41981.16 rows=501333 width=21) (actual time=16.366..16.366 rows=1 loops=1)
> Index Cond: (key1 >= 500000)
> Total runtime: 16.444 ms
>
>
> (stopped postgres; reset O/S cache; started postgres)
>
> test=# explain analyze SELECT * FROM test_select WHERE id = 500000;
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------------
> Index Scan using test_select_pkey on test_select (cost=0.00..8.36 rows=1 width=21) (actual time=23.072..23.074 rows=1 loops=1)
> Index Cond: (id = 500000)
> Total runtime: 23.192 ms
>
>
>
>
> On Mon, May 27, 2013 at 10:21 AM, Evgeny Shishkin <itparanoia(at)gmail(dot)com> wrote:
>
> On May 27, 2013, at 6:02 PM, John Mudd <johnbmudd(at)gmail(dot)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.
> >
>
> You created my_key after data loading, and PK was there all the time.
> 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=# 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)
> >
> > test=# explain analyze SELECT * FROM test_select WHERE id = 500000;
> > QUERY PLAN
> > ---------------------------------------------------------------------------------------------------------------------------------
> > Index Scan using test_select_pkey on test_select (cost=0.00..8.36 rows=1 width=21) (actual time=31.396..31.398 rows=1 loops=1)
> > Index Cond: (id = 500000)
> > Total runtime: 31.504 ms
> >
> >
> >
> > Schema:
> >
> > test=# \d test_select
> > Table "public.test_select"
> > Column | Type | 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=#
> >
> >
> >
> > Sample data:
> >
> > test=# SELECT * FROM test_select LIMIT 10;
> > id | key1 | key2 | key3 | data
> > ----+--------+--------+--------+------
> > 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 = psycopg2.connect('dbname=test')
> >
> > cur = conn.cursor()
> >
> > def random_int():
> > n = 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_select;
> >
> > CREATE TABLE test_select (
> > id SERIAL PRIMARY KEY,
> > key1 INTEGER,
> > key2 INTEGER,
> > key3 INTEGER,
> > data char(4)
> > );
> > ''')
> > conn.commit()
> >
> > n = 1000000
> > for i in range(n):
> > cur.execute("INSERT INTO test_select(key1, key2, key3, data) VALUES(%s, %s, %s, 'x')", random_key())
> > conn.commit()
> >
> > cur.execute('CREATE INDEX my_key ON test_select(key1, key2, key3, id)')
> > conn.commit()
> >
> > create_table()
> >
>
>