OK, the sample query is attached (hopefully attachments are allowed) as
"query.sql".
The "master table" definition is attached as "table1.sql".
The "detail table" definition is attached as "table2.sql".
The EXPLAIN (ANALYZE, BUFFERS) output is here:
http://explain.depesz.com/s/vd5
Let me know if I can provide anything else, and thank you again.
-AJ
On 6/25/2014 5:55 PM, Shaun Thomas wrote:
On 06/25/2014 04:40 PM, Aaron Weber wrote:
In the meantime, I guess I wasn't clear about some other particulars
The query's where clause is only an "IN", with a list of id's (those
I mentioned are the PK), and the join is explicitly on the PK (so,
indexed).
Indexed doesn't mean indexed if the wrong datatypes are used. We need
to see the table and index definitions, and a sample query with
EXPLAIN ANALYZE output.
An IN with 50 int values took 23sec to return (by way of example).
To me, this sounds like a sequence scan, or one of your key matches so
many rows, the random seeks are throwing off your performance. Of
course, I can't confirm that without EXPLAIN output.
select node.id as node_id,
node.version as node_version,
prop.qname_id as qname_id,
prop.locale_id as locale_id,
prop.list_index as list_index,
prop.actual_type_n as actual_type_n,
prop.persisted_type_n as persisted_type_n,
prop.boolean_value as boolean_value,
prop.long_value as long_value,
prop.float_value as float_value,
prop.double_value as double_value,
prop.string_value as string_value,
prop.serializable_value as serializable_value
from
alf_node node
join alf_node_properties prop on (prop.node_id = node.id)
WHERE node_id in
('175769', '175771', '175781', '175825', '175881', '175893', '175919', '175932', '175963', '175999', '176022', '176079', '176099', '176115', '176118', '176171', '176181', '176217', '176220', '176243', '176283', '176312', '176326', '176335', '176377', '176441', '176444', '176475', '176530', '176570', '176623', '176674', '176701', '176730', '176748', '176763', '176771', '176808', '176836', '176851', '176864', '176881', '176929', '176945', '176947', '176960', '177006', '177039', '177079', '177131', '177144')
-- Table: alf_node
-- DROP TABLE alf_node;
CREATE TABLE alf_node
(
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),
CONSTRAINT alf_node_pkey PRIMARY KEY (id),
CONSTRAINT fk_alf_node_acl FOREIGN KEY (acl_id)
REFERENCES alf_access_control_list (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_alf_node_loc FOREIGN KEY (locale_id)
REFERENCES alf_locale (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_alf_node_store FOREIGN KEY (store_id)
REFERENCES alf_store (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_alf_node_tqn FOREIGN KEY (type_qname_id)
REFERENCES alf_qname (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_alf_node_txn FOREIGN KEY (transaction_id)
REFERENCES alf_transaction (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
FILLFACTOR=90,
OIDS=FALSE
);
ALTER TABLE alf_node
OWNER TO alfresco;
-- Index: fk_alf_node_acl
-- DROP INDEX fk_alf_node_acl;
CREATE INDEX fk_alf_node_acl
ON alf_node
USING btree
(acl_id);
-- Index: fk_alf_node_loc
-- DROP INDEX fk_alf_node_loc;
CREATE INDEX fk_alf_node_loc
ON alf_node
USING btree
(locale_id);
-- Index: fk_alf_node_store
-- DROP INDEX fk_alf_node_store;
CREATE INDEX fk_alf_node_store
ON alf_node
USING btree
(store_id);
-- Index: fk_alf_node_tqn
-- DROP INDEX fk_alf_node_tqn;
CREATE INDEX fk_alf_node_tqn
ON alf_node
USING btree
(type_qname_id);
-- Index: fk_alf_node_txn
-- DROP INDEX fk_alf_node_txn;
CREATE INDEX fk_alf_node_txn
ON alf_node
USING btree
(transaction_id);
-- Index: idx_alf_node_del
-- DROP INDEX idx_alf_node_del;
CREATE INDEX idx_alf_node_del
ON alf_node
USING btree
(node_deleted);
-- Index: idx_alf_node_txn_del
-- DROP INDEX idx_alf_node_txn_del;
CREATE INDEX idx_alf_node_txn_del
ON alf_node
USING btree
(transaction_id, node_deleted);
-- Index: store_id
-- DROP INDEX store_id;
CREATE UNIQUE INDEX store_id
ON alf_node
USING btree
(store_id, uuid);
-- Table: alf_node_properties
-- DROP TABLE alf_node_properties;
CREATE TABLE alf_node_properties
(
node_id bigint NOT NULL,
actual_type_n integer NOT NULL,
persisted_type_n integer NOT NULL,
boolean_value boolean,
long_value bigint,
float_value real,
double_value double precision,
string_value character varying(1024),
serializable_value bytea,
qname_id bigint NOT NULL,
list_index integer NOT NULL,
locale_id bigint NOT NULL,
CONSTRAINT alf_node_properties_pkey PRIMARY KEY (node_id, qname_id, list_index, locale_id),
CONSTRAINT fk_alf_nprop_loc FOREIGN KEY (locale_id)
REFERENCES alf_locale (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_alf_nprop_n FOREIGN KEY (node_id)
REFERENCES alf_node (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_alf_nprop_qn FOREIGN KEY (qname_id)
REFERENCES alf_qname (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
FILLFACTOR=90,
OIDS=FALSE
);
ALTER TABLE alf_node_properties
OWNER TO alfresco;
-- Index: fk_alf_nprop_loc
-- DROP INDEX fk_alf_nprop_loc;
CREATE INDEX fk_alf_nprop_loc
ON alf_node_properties
USING btree
(locale_id);
-- Index: fk_alf_nprop_n
-- DROP INDEX fk_alf_nprop_n;
CREATE INDEX fk_alf_nprop_n
ON alf_node_properties
USING btree
(node_id);
-- Index: fk_alf_nprop_qn
-- DROP INDEX fk_alf_nprop_qn;
CREATE INDEX fk_alf_nprop_qn
ON alf_node_properties
USING btree
(qname_id);
