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

I sent the details as identified by pgAdmin III.

psql output shows this:
\d alf_node
                Table "public.alf_node"
      Column     |          Type          | Modifiers
----------------+------------------------+-----------
  id             | bigint                 | not null
  version        | bigint                 | not null
  store_id       | bigint                 | not null
  uuid           | character varying(36)  | not null
  transaction_id | bigint                 | not null
  node_deleted   | boolean                | not null
  type_qname_id  | bigint                 | not null
  locale_id      | bigint                 | not null
  acl_id         | bigint                 |
  audit_creator  | character varying(255) |
  audit_created  | character varying(30)  |
  audit_modifier | character varying(255) |
  audit_modified | character varying(30)  |
  audit_accessed | character varying(30)  |
Indexes:
     "alf_node_pkey" PRIMARY KEY, btree (id) CLUSTER
     "store_id" UNIQUE, btree (store_id, uuid)
     "fk_alf_node_acl" btree (acl_id)
     "fk_alf_node_loc" btree (locale_id)
     "fk_alf_node_store" btree (store_id)
     "fk_alf_node_tqn" btree (type_qname_id)
     "fk_alf_node_txn" btree (transaction_id)
     "idx_alf_node_del" btree (node_deleted)
     "idx_alf_node_txn_del" btree (transaction_id, node_deleted)
Foreign-key constraints:
     "fk_alf_node_acl" FOREIGN KEY (acl_id) REFERENCES
alf_access_control_list(id)
     "fk_alf_node_loc" FOREIGN KEY (locale_id) REFERENCES alf_locale(id)
     "fk_alf_node_store" FOREIGN KEY (store_id) REFERENCES alf_store(id)
     "fk_alf_node_tqn" FOREIGN KEY (type_qname_id) REFERENCES alf_qname(id)
     "fk_alf_node_txn" FOREIGN KEY (transaction_id) REFERENCES
alf_transaction(id)
Referenced by:
     TABLE "alf_child_assoc" CONSTRAINT "fk_alf_cass_cnode" FOREIGN KEY
(child_node_id) REFERENCES alf_node(id)
     TABLE "alf_child_assoc" CONSTRAINT "fk_alf_cass_pnode" FOREIGN KEY
(parent_node_id) REFERENCES alf_node(id)
     TABLE "alf_node_aspects" CONSTRAINT "fk_alf_nasp_n" FOREIGN KEY
(node_id) REFERENCES alf_node(id)
     TABLE "alf_node_assoc" CONSTRAINT "fk_alf_nass_snode" FOREIGN KEY
(source_node_id) REFERENCES alf_node(id)
     TABLE "alf_node_assoc" CONSTRAINT "fk_alf_nass_tnode" FOREIGN KEY
(target_node_id) REFERENCES alf_node(id)
     TABLE "alf_node_properties" CONSTRAINT "fk_alf_nprop_n" FOREIGN KEY
(node_id) REFERENCES alf_node(id)
     TABLE "alf_store" CONSTRAINT "fk_alf_store_root" FOREIGN KEY
(root_node_id) REFERENCES alf_node(id)
     TABLE "alf_subscriptions" CONSTRAINT "fk_alf_sub_node" FOREIGN KEY
(node_id) REFERENCES alf_node(id) ON DELETE CASCADE
     TABLE "alf_subscriptions" CONSTRAINT "fk_alf_sub_user" FOREIGN KEY
(user_node_id) REFERENCES alf_node(id) ON DELETE CASCADE
     TABLE "alf_usage_delta" CONSTRAINT "fk_alf_usaged_n" FOREIGN KEY
(node_id) REFERENCES alf_node(id)

This line of the output:
     "alf_node_pkey" PRIMARY KEY, btree (id) CLUSTER
would indicate to me that there is a PK on alf_node table, it is on
column "id", it is of type btree, and the table is clustered around that
index.

Am I reading this totally wrong?

The supporting table actually seems to have a multi-column PK defined,
and a separate btree index on node_id as you mentioned.

-AJ


On 6/26/2014 10:05 AM, Shaun Thomas wrote:
> 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.
>