From: Shaun Thomas <sthomas@optionshouse.com>
To: AJ Weber <aweber@comcast.net>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: how to improve perf of 131MM row table?
Date: Thu, 26 Jun 2014 09:05:35 -0500
Message-ID: <53AC28AF.1060500@optionshouse.com> (raw)
In-Reply-To: <53AC1F6E.4000700@comcast.net>
References: <53AB2CB1.8080201@comcast.net>
<53AB35CC.10202@optionshouse.com>
<1801a5d4-c3f1-4c0c-9c2c-ffb9d7f723a3@email.android.com>
<53AB4551.4070207@optionshouse.com>
<53AC1F6E.4000700@comcast.net>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 06/26/2014 08:26 AM, AJ Weber wrote:
> The "master table" definition is attached as "table1.sql".
> The "detail table" definition is attached as "table2.sql".
I'm not sure what you think a primary key is, but neither of these
tables have one. Primary keys are declared one of two ways:
CREATE TABLE foo
(
id BIGINT PRIMARY KEY,
col1 VARCHAR,
col2 INT
);
Or this:
CREATE TABLE foo
(
id BIGINT,
col1 VARCHAR,
col2 INT
);
ALTER TABLE foo ADD constraint pk_foo PRIMARY KEY (id);
On your alf_node_properties table, you only have an index on node_id
because you created one. If you look at your alf_node table, there is no
index on the id column at all. This is confirmed by the explain output
you attached:
Seq Scan on alf_node node (cost=0.00..227265.29 rows=5733429 width=16)
(actual time=0.013..2029.649 rows=5733888 loops=1)
Since it has no index, the database is reading the entire table to find
your matching values. Then it's using the index on node_id in the other
table to find the 'detail' matches, as seen here:
Bitmap Index Scan on fk_alf_nprop_n (cost=0.00..1240.00 rows=52790
width=0) (actual time=0.552..0.552 rows=1071 loops=1)
Add an actual primary key to your alf_node table, and your query
performance should improve substantially. But I also strongly suggest
you spend some time learning how to read an EXPLAIN plan, as that would
have made your problem obvious immediately.
Here's a link for your version:
http://www.postgresql.org/docs/9.0/static/sql-explain.html
You should still consider upgrading to the latest release of 9.0 too.
--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sthomas@optionshouse.com, aweber@comcast.net
Subject: Re: how to improve perf of 131MM row table?
In-Reply-To: <53AC28AF.1060500@optionshouse.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox