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