Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from
	<pgsql-performance-owner+M49980=pgsql-performance-ar@postgresql.org>)
	id 1UgyIy-000265-Vi for pgsql-performance@arkaria.postgresql.org;
	Mon, 27 May 2013 14:21:45 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.72) (envelope-from
	<pgsql-performance-owner+M49980=pgsql-performance-ar@postgresql.org>)
	id 1UgyIy-0006ph-Ei for pgsql-performance@arkaria.postgresql.org;
	Mon, 27 May 2013 14:21:44 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <itparanoia@gmail.com>) id 1UgyIw-0006oN-9j
	for pgsql-performance@postgresql.org; Mon, 27 May 2013 14:21:42 +0000
Received: from mail-lb0-f177.google.com ([209.85.217.177])
	by magus.postgresql.org with esmtp (Exim 4.80)
	(envelope-from <itparanoia@gmail.com>) id 1UgyIq-0002bF-5k
	for pgsql-performance@postgresql.org; Mon, 27 May 2013 14:21:41 +0000
Received: by mail-lb0-f177.google.com with SMTP id o10so6793796lbi.36
	for <pgsql-performance@postgresql.org>;
	Mon, 27 May 2013 07:21:35 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=content-type:mime-version:subject:from:in-reply-to:date:cc
	:content-transfer-encoding:message-id:references:to:x-mailer;
	bh=WztTLGbzE6qV9ISVeuU6PDhMrfEKh5NCgQkIB7p6CGg=;
	b=DQX2DZk7ebCy9ZGc6p3OB3GcctHSFPjUAJDUQTK9r2E62aMWsv0WcJmpzUBIpwafM/
	upcnitR3E6yXnKrp9/NepNebUq+Q7gZLqlz6KsYjWp9l32xKulcN9K4gGoM46oXRnfV/
	CuPrXGE9Ccaf+A4jnmHtuTuOA1kHmx2/joI+JZjoTF0tSZUciVSQx04u1ROCiL/pm/HJ
	v7ZD/f4Gcku39nKR9u4xHOp8KZw0nCBJY8F3MGm5h4Xr8PbNmDpBYeMnDJ25i5SetoQS
	Y4615yfJ1pb/BUM0W3GEUOai4QdgTVlBKU4xv7NzjbvZEJQxcfayJn6I9OKgd25nSH+d
	qyIw==
X-Received: by 10.152.87.116 with SMTP id w20mr14724919laz.0.1369664495392;
	Mon, 27 May 2013 07:21:35 -0700 (PDT)
Received: from [192.168.5.82] ([217.174.184.92])
	by mx.google.com with ESMTPSA id f9sm11665482lbf.4.2013.05.27.07.21.33
	for <multiple recipients>
	(version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
	Mon, 27 May 2013 07:21:34 -0700 (PDT)
Content-Type: text/plain; charset=iso-8859-1
Mime-Version: 1.0 (Mac OS X Mail 6.3 \(1503\))
Subject: Re: Slow SELECT by primary key? Postgres 9.1.2
From: Evgeny Shishkin <itparanoia@gmail.com>
In-Reply-To: 
 <CAGDMk9Hs003MrNAdMAUiCR3aUVX11JThX2-o3Fu2xD_hoPGYSg@mail.gmail.com>
Date: Mon, 27 May 2013 18:21:32 +0400
Cc: pgsql-performance@postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <6D8D2A94-C545-487F-BCB6-1E601F93E58C@gmail.com>
References: 
 <CAGDMk9Hs003MrNAdMAUiCR3aUVX11JThX2-o3Fu2xD_hoPGYSg@mail.gmail.com>
To: John Mudd <johnbmudd@gmail.com>
X-Mailer: Apple Mail (2.1503)
X-Pg-Spam-Score: -2.7 (--)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


On May 27, 2013, at 6:02 PM, John Mudd <johnbmudd@gmail.com> wrote:

> Postgres 9.1.2 on Ubuntu 12.04
>=20
> 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.=20
>=20

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



--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance