From: AJ Weber <aweber@comcast.net>
To: Shaun Thomas <sthomas@optionshouse.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: how to improve perf of 131MM row table?
Date: Thu, 26 Jun 2014 09:26:06 -0400
Message-ID: <53AC1F6E.4000700@comcast.net> (raw)
In-Reply-To: <53AB4551.4070207@optionshouse.com>
References: <53AB2CB1.8080201@comcast.net>
<53AB35CC.10202@optionshouse.com>
<1801a5d4-c3f1-4c0c-9c2c-ffb9d7f723a3@email.android.com>
<53AB4551.4070207@optionshouse.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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.
>
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performanceAttachments:query.sql (text/plain, 1.4K) download
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')
table1.sql (text/plain, 2.6K) download
-- 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);
table2.sql (text/plain, 1.5K) download
-- 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);
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: aweber@comcast.net, sthomas@optionshouse.com
Subject: Re: how to improve perf of 131MM row table?
In-Reply-To: <53AC1F6E.4000700@comcast.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox