pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Robin Ericsson <robin.ericsson@profecta.se>
To: pgsql-performance@postgresql.org
Subject: query problem
Date: Wed, 13 Oct 2004 11:21:11 +0200
Message-ID: <1097659271.24018.68.camel@pylver.localhost.nu.> (raw)

Hi,

I sent this to general earlier but I was redirected to performance.

The query have been running ok for quite some time, but after I did a
vacuum on the database, it's very very slow. This IN-query is only 2
ids. Before the problem that in was a subselect-query returning around
6-7 ids. The tables included in the query are described in database.txt.

status=# select count(id) from data;
  count
---------
 1577621
(1 row)

status=# select count(data_id) from data_values;
  count
---------
 9680931
(1 row)

I did run a new explain analyze on the query and found the attached
result. The obvious problem I see is a full index scan in
"idx_dv_data_id". I tried dropping and adding the index again, thats why
is't called "idx_data_values_data_id" in the dump.

status=# EXPLAIN ANALYZE
status-# SELECT
status-#         data.entered,
status-#         data.machine_id,
status-#         datatemplate_intervals.template_id,
status-#         data_values.value
status-# FROM
status-#         data, data_values, datatemplate_intervals
status-# WHERE
status-#         datatemplate_intervals.id = data_values.template_id AND
status-#         data_values.data_id = data.id AND
status-#         data.machine_id IN (2,3) AND
status-#         current_timestamp::timestamp - interval '60 seconds' <
data.entered;



Regards,
Robin


-- 
Robin Ericsson <robin.ericsson@profecta.se>
Profecta HB

Attachments:

explain2.txt (text/plain, 1.2K) download Hash Join (cost=28646.01..274260.15 rows=555706 width=24) (actual time=102323.087..102323.196 rows=5 loops=1) Hash Cond: ("outer".template_id = "inner".id) -> Merge Join (cost=28644.09..265922.62 rows=555706 width=24) (actual time=102322.632..102322.709 rows=5 loops=1) Merge Cond: ("outer".data_id = "inner".id) -> Index Scan using idx_dv_data_id on data_values (cost=0.00..205034.19 rows=9580032 width=16) (actual time=17.503..86263.130 rows=9596747 loops=1) -> Sort (cost=28644.09..28870.83 rows=90697 width=16) (actual time=0.829..0.835 rows=1 loops=1) Sort Key: data.id -> Index Scan using idx_d_entered on data (cost=0.00..20202.81 rows=90697 width=16) (actual time=0.146..0.185 rows=1 loops=1) Index Cond: (((('now'::text)::timestamp(6) with time zone)::timestamp without time zone - '00:01:00'::interval) < entered) Filter: ((machine_id = 2) OR (machine_id = 3)) -> Hash (cost=1.74..1.74 rows=74 width=8) (actual time=0.382..0.382 rows=0 loops=1) -> Seq Scan on datatemplate_intervals (cost=0.00..1.74 rows=74 width=8) (actual time=0.024..0.250 rows=74 loops=1) Total runtime: 102323.491 ms (13 rows)
database.txt (text/plain, 2.6K) download status=# \d data Table "public.data" Column | Type | Modifiers ------------+-----------------------------+-------------------------------------------- id | integer | not null default nextval('data_seq'::text) updated | timestamp without time zone | entered | timestamp without time zone | machine_id | integer | datas | character varying(512)[] | Indexes: "data_pkey" primary key, btree (id) "idx_d_entered" btree (entered) "idx_d_machine_id" btree (machine_id) Foreign-key constraints: "machine_id" FOREIGN KEY (machine_id) REFERENCES machines(id) Triggers: data_datestamp BEFORE INSERT OR UPDATE ON data FOR EACH ROW EXECUTE PROCEDURE datestamp_e() status=# \d data_values Table "public.data_values" Column | Type | Modifiers -------------+-----------------------------+----------- updated | timestamp without time zone | entered | timestamp without time zone | data_id | integer | template_id | integer | value | character varying(512) | Indexes: "idx_data_values_data_id" btree (data_id) "idx_dv_template_id" btree (template_id) Foreign-key constraints: "data_id" FOREIGN KEY (data_id) REFERENCES data(id) "template_id" FOREIGN KEY (template_id) REFERENCES datatemplate_intervals(id) Triggers: data_values_datestamp BEFORE INSERT OR UPDATE ON data_values FOR EACH ROW EXECUTE PROCEDURE datestamp_e() status=# \d datatemplate_intervals Table "public.datatemplate_intervals" Column | Type | Modifiers -------------+-----------------------------+-------------------------------------------------------------- id | integer | not null default nextval('datatemplate_intervals_seq'::text) updated | timestamp without time zone | entered | timestamp without time zone | machine_id | integer | template_id | integer | interval | integer | Indexes: "datatemplate_intervals_pkey" primary key, btree (id) "idx_di_machine_id" btree (machine_id) "idx_di_template_id" btree (template_id) Foreign-key constraints: "machine_id" FOREIGN KEY (machine_id) REFERENCES machines(id) "template_id" FOREIGN KEY (template_id) REFERENCES datatemplates(id) Triggers: datatemplate_intervals_datestamp BEFORE INSERT OR UPDATE ON datatemplate_intervals FOR EACH ROW EXECUTE PROCEDURE datestamp_e()

view thread (9+ messages)  latest in thread

Message-ID: <1097659271.24018.68.camel@pylver.localhost.nu.>
Permalink:  ../1097659271.24018.68.camel@pylver.localhost.nu./
Also on:    postgresql.org/message-id/1097659271.24018.68.camel@pylver.localhost.nu.

 · 

reply

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: robin.ericsson@profecta.se
  Subject: Re: query problem
  In-Reply-To: <1097659271.24018.68.camel@pylver.localhost.nu.>

* 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