Message-ID: <53AC28AF.1060500@optionshouse.com>
Date: Thu, 26 Jun 2014 09:05:35 -0500
From: Shaun Thomas <sthomas@optionshouse.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.5.0
MIME-Version: 1.0
To: AJ Weber <aweber@comcast.net>, "pgsql-performance@postgresql.org"
 <pgsql-performance@postgresql.org>
Subject: Re: how to improve perf of 131MM row table?
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>
In-Reply-To: <53AC1F6E.4000700@comcast.net>
Content-Type: text/plain; charset="windows-1252"; format=flowed
content-transfer-encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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