Hallo,
ich habe folgende Strukturen:
messdaten=# \d z_heizung_entries;
Table "public.z_heizung_entries"
Column | Type | Modifiers
-------------+--------------------------+---------------------------------------------------------------------
id | integer | not null default nextval(('z_heizung_entries_seq'::text)::regclass)
time_string | timestamp with time zone | not null
Indexes:
"z_heizung_entries_pkey" PRIMARY KEY, btree (id)
messdaten=# \d z_heizung_data;
Table "public.z_heizung_data"
Column | Type | Modifiers
-------------+---------+-------------------------------------------------------------
entry_id | integer | not null default currval('z_heizung_entries_seq'::regclass)
sensor_id | integer | not null
sensor_temp | real |
Indexes:
"z_heizung_data_idx_entry_id" btree (entry_id)
"z_heizung_data_idx_sensor_id" btree (sensor_id)
messdaten=# \d z_heizung_sensoren;
Table "public.z_heizung_sensoren"
Column | Type | Modifiers
-------------+-----------------------+--------------------------------------------------------------------
id | integer | not null default nextval(('z_heizung_sensor_seq'::text)::regclass)
sensor_id | character varying(16) | not null default ''::character varying
sensor_name | character varying(50) | not null default ''::character varying
sensor_type | character varying(10) | not null default ''::character varying
Indexes:
"z_heizung_sensoren_pkey" PRIMARY KEY, btree (id)
"z_heizung_sensoren_idx_sensor_id" btree (sensor_id)
"z_heizung_sensoren_idx_sensor_name" btree (sensor_name)
Um nun an meine Daten zu kommen, kam folgende Query pro Sensor (Ich habe 10 Sensoren) zustande:
messdaten=# SELECT s.sensor_name, d.sensor_temp, e.time_string FROM z_heizung_entries AS e JOIN z_heizung_data AS d ON d.entry_id = e.id JOIN z_heizung_sensoren AS s ON s.id = d.sensor_id WHERE extract(epoch from e.time_string) > 1300445033 AND extract(epoch from e.time_string) < 1300631433 AND d.sensor_id = 1;
sensor_name | sensor_temp | time_string
-------------+-------------+------------------------
H_Ruecklauf | 21.06 | 2011-03-18 13:22:01+01
H_Ruecklauf | 21.13 | 2011-03-18 13:21:02+01
H_Ruecklauf | 21.13 | 2011-03-18 13:20:01+01
H_Ruecklauf | 21.13 | 2011-03-18 13:19:02+01
H_Ruecklauf | 21.19 | 2011-03-18 13:18:01+01
H_Ruecklauf | 21.13 | 2011-03-18 13:17:01+01
H_Ruecklauf | 21.13 | 2011-03-18 13:16:01+01
H_Ruecklauf | 21.13 | 2011-03-18 13:15:02+01
H_Ruecklauf | 21.13 | 2011-03-18 13:14:01+01
messdaten=# explain analyse SELECT s.sensor_name, d.sensor_temp, e.time_string FROM z_heizung_entries AS e JOIN z_heizung_data AS d ON d.entry_id = e.id JOIN z_heizung_sensoren AS s ON s.id = d.sensor_id WHERE extract(epoch from e.time_string) > 1300445033 AND extract(epoch from e.time_string) < 1300631433 AND d.sensor_id = 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..3600.53 rows=390 width=130) (actual time=0.050..127.463 rows=1785 loops=1)
-> Seq Scan on z_heizung_sensoren s (cost=0.00..1.14 rows=1 width=122) (actual time=0.009..0.016 rows=1 loops=1)
Filter: (1 = id)
-> Nested Loop (cost=0.00..3595.49 rows=390 width=16) (actual time=0.035..124.319 rows=1785 loops=1)
-> Seq Scan on z_heizung_entries e (cost=0.00..2208.04 rows=435 width=12) (actual time=0.010..89.423 rows=1785 loops=1)
Filter: ((date_part('epoch'::text, time_string) > 1300445033::double precision) AND (date_part('epoch'::text, time_string) < 1300631433::double precision))
-> Index Scan using z_heizung_data_idx_entry_id on z_heizung_data d (cost=0.00..3.18 rows=1 width=12) (actual time=0.009..0.015 rows=1 loops=1785)
Index Cond: (d.entry_id = "outer".id)
Filter: (sensor_id = 1)
Total runtime: 129.007 ms
(10 rows)
Erklärung: Die Zeiten hinter dem extract() werden von einem PHP-Script eingesetzt, und danach zur PG gesendet.
Problem: Ich kann das Seq-Scan auf der ‘z_heizung_entries’ nicht vermeiden. Das Abfragen aller 10 Sensoren hat zur Folge das ich 1,5 Sek. warten muss, bis was kommt (WWW-Seite mit PHP-Script). Wie man schön erkennen kann folgt hier eine Typenkonversion, die mich zum verzweilfen bringt Ich hätte gerne einen Index
Hätte ich das time_string feld vielleicht doch besser als “integer” gewählt, um dort einfach den timestamp zu plazieren? Oder gehe ich das extract() falsch an?
Gruss,
Christian