Hello all,
I have been pulling my hair out over the last few days trying to get any useful performance out of the following
painfully slow query.
The query is JPA created, I've just cleaned the aliases to make it more readable.
Using 'distinct' or 'group by' deliver about the same results, but 'distinct' is marginally better.
Hardware is pretty low end (a test box), but is mostly dedicated to PostgreSQL.
The box spec and configuration is included at the end of this post - Some of the values have been changed just to see if
things get better.
Inserts have also become extremely slow. I was expecting a drop off when the database grew out of memory, but not this much.
Am I really missing the target somewhere?
Any help and or suggestions will be very much appreciated.
Best regards,
Andy.
http://explain.depesz.com/s/cfb
select distinct tr.nr as tnr
, tr.time_end as tend
, c.id_board as cb
, c.id_board_mini as cbm
, ti.id_test_result as itr
from test_item ti
, test_result tr
, component c
, recipe_version rv
where ti.id_test_result = tr.id
and ti.id_component = c.id
and tr.id_recipe_version = rv.id
and (rv.id_recipe in ('6229bf04-ae38-11e1-a955-0021974df2b2'))
and tr.time_end <> cast('1970-01-01 01:00:00.000' as timestamp)
and tr.time_begin >= cast('2012-10-22 00:00:14.383' as timestamp)
and ti.type = 'Component'
--group by tr.nr , tr.time_end , c.id_board , c.id_board_mini , ti.id_test_result
order by tr.time_end asc limit 10000
-- ########################
-- Table: test_item
-- Table Size 2119 MB
-- Indexes Size 1845 MB
-- Live Tuples 6606871
-- DROP TABLE test_item;
CREATE TABLE test_item
(
id character varying(36) NOT NULL,
angle double precision NOT NULL,
description character varying(1000),
designation character varying(128) NOT NULL,
failed boolean NOT NULL,
node integer NOT NULL,
nr integer NOT NULL,
nr_verified integer,
occurred timestamp without time zone NOT NULL,
ocr character varying(384),
pack_industry_name character varying(255),
passed boolean NOT NULL,
pin character varying(8),
pos_valid boolean NOT NULL,
pos_x double precision NOT NULL,
pos_y double precision NOT NULL,
pos_z double precision NOT NULL,
qref character varying(255) NOT NULL,
reference_id character varying(128) NOT NULL,
repaired boolean NOT NULL,
size_x double precision NOT NULL,
size_y double precision NOT NULL,
sort integer NOT NULL,
subtype character varying(20) NOT NULL,
type character varying(20) NOT NULL,
valid boolean NOT NULL,
version integer,
id_component character varying(36),
id_pack character varying(36),
id_test_item character varying(36),
id_test_result character varying(36) NOT NULL,
CONSTRAINT test_item_pkey PRIMARY KEY (id),
CONSTRAINT fk_test_item_component FOREIGN KEY (id_component)
REFERENCES component (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_test_item_pack FOREIGN KEY (id_pack)
REFERENCES pack (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_test_item_test_item FOREIGN KEY (id_test_item)
REFERENCES test_item (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_test_item_test_result FOREIGN KEY (id_test_result)
REFERENCES test_result (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
-- Index: ix_test_item_c
-- DROP INDEX ix_test_item_c;
CREATE INDEX ix_test_item_c
ON test_item
USING btree
(type COLLATE pg_catalog."default")
WHERE type::text = 'Component'::text;
-- Index: ix_test_item_id_component
-- DROP INDEX ix_test_item_id_component;
CREATE INDEX ix_test_item_id_component
ON test_item
USING btree
(id_component COLLATE pg_catalog."default");
-- Index: ix_test_item_id_test_item
-- DROP INDEX ix_test_item_id_test_item;
CREATE INDEX ix_test_item_id_test_item
ON test_item
USING btree
(id_test_item COLLATE pg_catalog."default");
-- Index: ix_test_item_id_test_result
-- DROP INDEX ix_test_item_id_test_result;
CREATE INDEX ix_test_item_id_test_result
ON test_item
USING btree
(id_test_result COLLATE pg_catalog."default");
-- Index: ix_test_item_type
-- DROP INDEX ix_test_item_type;
CREATE INDEX ix_test_item_type
ON test_item
USING btree
(type COLLATE pg_catalog."default");
-- Table: test_result
-- DROP TABLE test_result;
CREATE TABLE test_result
(
id character varying(36) NOT NULL,
description character varying(255) NOT NULL,
name character varying(100) NOT NULL,
nr integer NOT NULL,
state integer NOT NULL,
time_begin timestamp without time zone NOT NULL,
time_end timestamp without time zone NOT NULL,
version integer,
id_machine character varying(36) NOT NULL,
id_recipe_version character varying(36) NOT NULL,
CONSTRAINT test_result_pkey PRIMARY KEY (id),
CONSTRAINT fk_test_result_machine FOREIGN KEY (id_machine)
REFERENCES machine (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_test_result_recipe_version FOREIGN KEY (id_recipe_version)
REFERENCES recipe_version (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
-- Index: ix_test_result_id_id_recipe_version
-- DROP INDEX ix_test_result_id_id_recipe_version;
CREATE INDEX ix_test_result_id_id_recipe_version
ON test_result
USING btree
(id COLLATE pg_catalog."default", id_recipe_version COLLATE pg_catalog."default");
-- Index: ix_test_result_id_id_recipe_version_time_end_time_begin
-- DROP INDEX ix_test_result_id_id_recipe_version_time_end_time_begin;
CREATE INDEX ix_test_result_id_id_recipe_version_time_end_time_begin
ON test_result
USING btree
(id COLLATE pg_catalog."default", id_recipe_version COLLATE pg_catalog."default", time_end, time_begin);
-- Index: ix_test_result_id_machine
-- DROP INDEX ix_test_result_id_machine;
CREATE INDEX ix_test_result_id_machine
ON test_result
USING btree
(id_machine COLLATE pg_catalog."default");
-- Index: ix_test_result_id_recipe_version
-- DROP INDEX ix_test_result_id_recipe_version;
CREATE INDEX ix_test_result_id_recipe_version
ON test_result
USING btree
(id_recipe_version COLLATE pg_catalog."default");
-- Index: ix_test_result_id_recipe_version_time
-- DROP INDEX ix_test_result_id_recipe_version_time;
CREATE INDEX ix_test_result_id_recipe_version_time
ON test_result
USING btree
(id_recipe_version COLLATE pg_catalog."default", time_end, time_begin);
-- Index: ix_test_result_time_begin
-- DROP INDEX ix_test_result_time_begin;
CREATE INDEX ix_test_result_time_begin
ON test_result
USING btree
(time_begin);
-- Index: ix_test_result_time_end
-- DROP INDEX ix_test_result_time_end;
CREATE INDEX ix_test_result_time_end
ON test_result
USING btree
(time_end);
-- Index: ix_test_result_time_id_recipe_version
-- DROP INDEX ix_test_result_time_id_recipe_version;
CREATE INDEX ix_test_result_time_id_recipe_version
ON test_result
USING btree
(time_end, id_recipe_version COLLATE pg_catalog."default");
-- Table: component
-- DROP TABLE component;
CREATE TABLE component
(
id character varying(36) NOT NULL,
cad_angle double precision NOT NULL,
cad_part character varying(100),
cad_type character varying(100),
cad_x double precision NOT NULL,
cad_y double precision NOT NULL,
cad_z double precision NOT NULL,
cid integer NOT NULL,
comment character varying(255),
name character varying(80) NOT NULL,
ocr character varying(384),
pin_count integer NOT NULL,
pos_angle double precision NOT NULL,
pos_height double precision NOT NULL,
pos_width double precision NOT NULL,
pos_x double precision NOT NULL,
pos_y double precision NOT NULL,
pos_z double precision NOT NULL,
ref_des character varying(100),
ref_id character varying(100),
type character varying(25) NOT NULL,
version integer,
id_board character varying(36),
id_board_mini character varying(36),
id_frame character varying(36),
id_pack character varying(36),
id_recipe_version character varying(36) NOT NULL,
global_x double precision NOT NULL,
global_y double precision NOT NULL,
global_angle double precision NOT NULL,
CONSTRAINT component_pkey PRIMARY KEY (id),
CONSTRAINT fk_component_board FOREIGN KEY (id_board)
REFERENCES board (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_component_board_mini FOREIGN KEY (id_board_mini)
REFERENCES board_mini (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_component_frame FOREIGN KEY (id_frame)
REFERENCES frame (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_component_pack FOREIGN KEY (id_pack)
REFERENCES pack (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_component_recipe_version FOREIGN KEY (id_recipe_version)
REFERENCES recipe_version (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
-- Index: ix_component_cid
-- DROP INDEX ix_component_cid;
CREATE INDEX ix_component_cid
ON component
USING btree
(cid);
-- Index: ix_component_id_board
-- DROP INDEX ix_component_id_board;
CREATE INDEX ix_component_id_board
ON component
USING btree
(id_board COLLATE pg_catalog."default");
-- Index: ix_component_id_board_mini
-- DROP INDEX ix_component_id_board_mini;
CREATE INDEX ix_component_id_board_mini
ON component
USING btree
(id_board_mini COLLATE pg_catalog."default");
-- Index: ix_component_id_frame
-- DROP INDEX ix_component_id_frame;
CREATE INDEX ix_component_id_frame
ON component
USING btree
(id_frame COLLATE pg_catalog."default");
-- Index: ix_component_id_pack
-- DROP INDEX ix_component_id_pack;
CREATE INDEX ix_component_id_pack
ON component
USING btree
(id_pack COLLATE pg_catalog."default");
-- Index: ix_component_id_recipe_version
-- DROP INDEX ix_component_id_recipe_version;
CREATE INDEX ix_component_id_recipe_version
ON component
USING btree
(id_recipe_version COLLATE pg_catalog."default");
-- Table: recipe_version
-- DROP TABLE recipe_version;
CREATE TABLE recipe_version
(
id character varying(36) NOT NULL,
certified smallint NOT NULL,
deprecated boolean NOT NULL,
edit timestamp without time zone NOT NULL,
name character varying(255),
qpc_identifier integer NOT NULL,
recipe_version integer NOT NULL,
revision character varying(150),
version integer,
id_comment character varying(36),
id_recipe character varying(36) NOT NULL,
id_recipe_version character varying(36),
intention smallint NOT NULL DEFAULT 0,
CONSTRAINT recipe_version_pkey PRIMARY KEY (id),
CONSTRAINT fk_recipe_version_comment FOREIGN KEY (id_comment)
REFERENCES comment (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_recipe_version_recipe FOREIGN KEY (id_recipe)
REFERENCES recipe (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_recipe_version_recipe_version FOREIGN KEY (id_recipe_version)
REFERENCES recipe_version (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
-- Index: ix_recipe_version_certified
-- DROP INDEX ix_recipe_version_certified;
CREATE INDEX ix_recipe_version_certified
ON recipe_version
USING btree
(certified);
-- Index: ix_recipe_version_id_id_recipe
-- DROP INDEX ix_recipe_version_id_id_recipe;
CREATE INDEX ix_recipe_version_id_id_recipe
ON recipe_version
USING btree
(id COLLATE pg_catalog."default", id_recipe COLLATE pg_catalog."default");
-- Index: ix_recipe_version_id_recipe
-- DROP INDEX ix_recipe_version_id_recipe;
CREATE INDEX ix_recipe_version_id_recipe
ON recipe_version
USING btree
(id_recipe COLLATE pg_catalog."default");
-- Index: ix_recipe_version_name
-- DROP INDEX ix_recipe_version_name;
CREATE INDEX ix_recipe_version_name
ON recipe_version
USING btree
(name COLLATE pg_catalog."default");
-- Index: ix_recipe_version_recipe_version
-- DROP INDEX ix_recipe_version_recipe_version;
CREATE INDEX ix_recipe_version_recipe_version
ON recipe_version
USING btree
(recipe_version);
-- Index: ix_recipe_version_recipe_version_test
-- DROP INDEX ix_recipe_version_recipe_version_test;
CREATE INDEX ix_recipe_version_recipe_version_test
ON recipe_version
USING btree
(id_recipe COLLATE pg_catalog."default", certified, id COLLATE pg_catalog."default");
-- ########################
"version";"PostgreSQL 9.2.1, compiled by Visual C++ build 1600, 32-bit"
"autovacuum";"on"
"autovacuum_analyze_scale_factor";"0.01"
"autovacuum_analyze_threshold";"20"
"autovacuum_max_workers";"5"
"autovacuum_naptime";"15s"
"autovacuum_vacuum_cost_delay";"20ms"
"autovacuum_vacuum_scale_factor";"0.01"
"autovacuum_vacuum_threshold";"20"
"bytea_output";"escape"
"checkpoint_completion_target";"0.9"
"checkpoint_segments";"64"
"client_encoding";"UNICODE"
"client_min_messages";"notice"
"cpu_index_tuple_cost";"0.001"
"cpu_operator_cost";"0.0005"
"cpu_tuple_cost";"0.003"
"deadlock_timeout";"30s"
"default_statistics_target";"200"
"effective_cache_size";"3GB"
"escape_string_warning";"off"
"external_pid_file";"orprovision.pid"
"from_collapse_limit";"12"
"fsync";"off"
"geqo_threshold";"14"
"join_collapse_limit";"12"
"lc_collate";"German_Germany.1252"
"lc_ctype";"German_Germany.1252"
"listen_addresses";"*"
"log_autovacuum_min_duration";"10s"
"log_checkpoints";"on"
"log_destination";"stderr"
"log_filename";"day-%d.log"
"log_line_prefix";"%t:%r:%u@%d:[%p]: "
"log_lock_waits";"on"
"log_min_duration_statement";"3s"
"log_min_error_statement";"log"
"log_min_messages";"log"
"log_rotation_size";"1MB"
"log_statement";"none"
"log_truncate_on_rotation";"on"
"logging_collector";"on"
"maintenance_work_mem";"256MB"
"max_connections";"50"
"max_locks_per_transaction";"500"
"max_prepared_transactions";"250"
"max_stack_depth";"2MB"
"port";"6464"
"random_page_cost";"5"
"seq_page_cost";"2"
"server_encoding";"UTF8"
"shared_buffers";"256MB"
"statement_timeout";"40min"
"synchronous_commit";"off"
"wal_buffers";"16MB"
"work_mem";"16MB"
Operating System: Windows 7 Home Premium 32-bit (6.1, Build 7601) Service Pack 1 (7601.win7sp1_gdr.120830-0333)
Language: German (Regional Setting: German)
System Manufacturer: Acer
System Model: Aspire X1700
BIOS: Default System BIOS
Processor: Intel(R) Core(TM)2 Quad CPU Q8200 @ 2.33GHz (4 CPUs), ~2.3GHz
Memory: 4096MB RAM
Available OS Memory: 3072MB RAM
Page File: 3328MB used, 2811MB available
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
