Thread: Slow query, where am I going wrong?

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

Andy wrote:
> 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

The estimate on the join between recipe_version and test_result is not
good.

Maybe things will improve if you increase the statistics on
test_result.id_recipe_version.

If that does not help, maybe the nested loop join that takes
all your time can be sped up with the following index:

CREATE INDEX any_name ON test_item (id_test_result, type);

But I would not expect much improvement there.

BTW, you seem to have an awful lot of indexes defined, some
of which seem redundant.

Yours,
Laurenz Albe

Thanks very much Laurenz.

I'll put your suggestions into motion right away and let you know the
results.


Albe Laurenz *EXTERN* wrote
> BTW, you seem to have an awful lot of indexes defined, some
> of which seem redundant.

I am in the process of pruning unused/useless indexes on this database - So
many of them will be dropped. Most of them are not in production and are
past play things on this test system.

The actual production test_item table gets about 140k inserts a day (avg).
Having this test system slow, dirty and bloated is quite good as it helps us
identify potential bottlenecks before they hit production. Partitioning is
also on the cards, but solving this current issue is only going to help.

Thanks again.

Andy




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Slow-query-where-am-I-going-wrong-tp5730015p5730025.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

A marginal improvement.

http://explain.depesz.com/s/y63

I am going to normalize the table some more before partitioning.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Slow-query-where-am-I-going-wrong-tp5730015p5730059.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

AndyG wrote:
> A marginal improvement.
>
> http://explain.depesz.com/s/y63

That's what I thought.

Increasing the statistics for test_result.id_recipe_version
had no effect?

> I am going to normalize the table some more before partitioning.

How do you think that partitioning will help?

Yours,
Laurenz Albe

Albe Laurenz *EXTERN* wrote
> Increasing the statistics for test_result.id_recipe_version
> had no effect?
>
>> I am going to normalize the table some more before partitioning.
>
> How do you think that partitioning will help?

I increased the statistics in steps up to 5000 (with vacuum analyse) - Seems
to be as good as it gets.

http://explain.depesz.com/s/z2a

The simulated data is about a months worth. Partitioning is only really
expected to help on insert, but that's pretty critical for us.

At the moment test_item contains way too much repeated data IMHO, and I will
address that asap (is going to hurt ).

I will also look into creating an aggregate table to hold the 'distinct'
values.

Andy.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Slow-query-where-am-I-going-wrong-tp5730015p5730140.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

AndyG wrote:
>> Increasing the statistics for test_result.id_recipe_version
>> had no effect?

> I increased the statistics in steps up to 5000 (with vacuum analyse) -
Seems
> to be as good as it gets.
>
> http://explain.depesz.com/s/z2a

Just out of curiosity, do you get a better plan with
enable_nestloop=off?
Not that I think it would be a good idea to change that
setting in general.

Yours,
Laurenz Albe

Much better...

http://explain.depesz.com/s/uFi



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Slow-query-where-am-I-going-wrong-tp5730015p5730145.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

But why? Is there a way to force the planner into this?



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Slow-query-where-am-I-going-wrong-tp5730015p5730151.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

> But why? Is there a way to force the planner into this?

I don't know enough about the planner to answer the "why",
but the root of the problem seems to be the mis-estimate
for the join between test_result and recipe_version
(1348 instead of 21983 rows).

That makes the planner think that a nested loop join
would be cheaper, but it really is not.

I had hoped that improving statistics would improve that
estimate.

The only way to force the planner to do it that way is
to set enable_nestloop=off, but only for that one query.
And even that is a bad idea, because for different
constant values or when the table data change, a nested
loop join might actually be the best choice.

I don't know how to solve that problem.

Yours,
Laurenz Albe

Externalizing the limit has improved the speed a lot. Distinct is half a
second faster than group by.

http://explain.depesz.com/s/vP1

with tmp as (
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-27 08:00:17.045' 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)
select * from tmp
limit 10000



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Slow-query-where-am-I-going-wrong-tp5730015p5730185.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.