Re: how to improve perf of 131MM row table?

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(at)optionshouse(dot)com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email