index auf timestamptz erstellen um "Seq Scan" zu vermeiden, Jemand eine Idee?

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 :p Ich hätte gerne einen Index :slight_smile:

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

Versuch doch mal diesen Index:

CREATE INDEX epoch_time_string
   ON z_heizung_entries ( extract(epoch from time_string))

Ich versteh’ nur nicht warum Du das so umständlich vergleichst.
Ich persönlich finde ein

WHERE time_string > TIMESTAMP '2011-03-18 11:43:53.0'

deutlich lesbarer. Dann sollte auch ein “normaler” index auf die Spalte reichen.

Solltest Du den Vergleichswert nur als integer bekommen, dann bleibst Du entweder bei dem function-based-index und extract(epoc…) oder erstellst einen normalen Index auf time_string und verwendest

WHERE time_string > to_timestamp(1300445033 )

Nebenbei: ich finde ja einen Spaltenname der mit _string aufhört für ein Feld vom Typ timestamp irgendwie verwirrend…

Huhu,
das ich den TIMESTAMP-cast so verwenden konnte, wusste ich nicht (wer lesen kann, ist klar im vorteil :wink: )…

messdaten=# create index z_heizung_entries_ts ON z_heizung_entries USING btree (time_string);
CREATE INDEX

Der erste Versuch mittels ‘to_timestamp’ endete so:

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 e.time_string > to_timestamp(1300445033) AND e.time_string <  to_timestamp(1300631433) AND d.sensor_id = 1;
                                                                                    QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=1002.21..6715.56 rows=2500 width=130) (actual time=24.083..240.571 rows=1785 loops=1)
   ->  Seq Scan on z_heizung_sensoren s  (cost=0.00..1.14 rows=1 width=122) (actual time=0.008..0.017 rows=1 loops=1)
         Filter: (1 = id)
   ->  Hash Join  (cost=1002.21..6689.42 rows=2500 width=16) (actual time=24.067..237.681 rows=1785 loops=1)
         Hash Cond: ("outer".entry_id = "inner".id)
         ->  Bitmap Heap Scan on z_heizung_data d   (cost=455.90..5332.69 rows=78543 width=12) (actual time=18.963..147.781  rows=79068 loops=1)
               Recheck Cond: (sensor_id = 1)
               ->  Bitmap Index Scan on z_heizung_data_idx_sensor_id   (cost=0.00..455.90 rows=78543 width=0) (actual time=17.408..17.408  rows=79068 loops=1)
                     Index Cond: (sensor_id = 1)
         ->  Hash  (cost=539.36..539.36 rows=2779 width=12) (actual time=5.074..5.074 rows=1785 loops=1)
               ->  Bitmap Heap Scan on z_heizung_entries e   (cost=25.67..539.36 rows=2779 width=12) (actual time=0.621..2.800  rows=1785 loops=1)
                     Recheck Cond: ((time_string > '2011-03-18  11:43:53+01'::timestamp with time zone) AND (time_string <  '2011-03-20 15:30:33+01'::timestamp with time zone))
                     ->  Bitmap Index Scan on z_heizung_entries_ts   (cost=0.00..25.67 rows=2779 width=0) (actual time=0.595..0.595 rows=1785  loops=1)
                           Index Cond: ((time_string > '2011-03-18  11:43:53+01'::timestamp with time zone) AND (time_string <  '2011-03-20 15:30:33+01'::timestamp with time zone))
 Total runtime: 242.051 ms
(15 rows)

Der zweite mit dem Vergleich von TIMESTAMP:

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 e.time_string > TIMESTAMP '2011-03-25 00:00:00+01' AND  e.time_string < TIMESTAMP '2011-03-25 23:59:59+01' AND d.sensor_id =  1;
                                                                              QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..8.63 rows=1 width=130) (actual time=0.111..49.768 rows=1335 loops=1)
   ->  Nested Loop  (cost=0.00..7.49 rows=1 width=16) (actual time=0.101..32.969 rows=1335 loops=1)
         ->  Index Scan using z_heizung_entries_ts on  z_heizung_entries e  (cost=0.00..4.30 rows=1 width=12) (actual  time=0.074..6.049 rows=1336 loops=1)
               Index Cond: ((time_string > '2011-03-25  00:00:00'::timestamp without time zone) AND (time_string <  '2011-03-25 23:59:59'::timestamp without time zone))
         ->  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.012..0.015 rows=1 loops=1336)
               Index Cond: (d.entry_id = "outer".id)
               Filter: (sensor_id = 1)
   ->  Seq Scan on z_heizung_sensoren s  (cost=0.00..1.14 rows=1 width=122) (actual time=0.002..0.008 rows=1 loops=1335)
         Filter: (1 = id)
 Total runtime: 50.934 ms
(10 rows)

Nun fing der ‘guten morgen kaffee’ im Zusammenhang mit der Antwort an zu wirken :smiley: :

CREATE OR REPLACE FUNCTION myextract(timestamptz)
  RETURNS double precision AS 'select extract(epoch from $1);'
  LANGUAGE SQL
  IMMUTABLE
  RETURNS NULL ON NULL INPUT;

CREATE UNIQUE INDEX z_heizung_idx_ts_f ON z_heizung_entries(myextract(time_string));

^-- Das ging mir gestern Abend / Nacht nicht in den Kopf, das Resultat:

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 myextract(e.time_string) > 1301007600 AND myextract(e.time_string) < 1301093999 AND d.sensor_id = 1;
                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=5.62..1896.19 rows=393 width=130) (actual time=0.540..29.381 rows=1335 loops=1)
   ->  Seq Scan on z_heizung_sensoren s  (cost=0.00..1.14 rows=1 width=122) (actual time=0.008..0.015 rows=1 loops=1)
         Filter: (1 = id)
   ->  Nested Loop  (cost=5.62..1891.12 rows=393 width=16) (actual time=0.524..27.062 rows=1335 loops=1)
         ->  Bitmap Heap Scan on z_heizung_entries e  (cost=5.62..497.15 rows=437 width=12) (actual time=0.473..2.020 rows=1336 loops=1)
               Recheck Cond: ((myextract(time_string) > 1301007600::double precision) AND (myextract(time_string) < 1301093999::double precision))
               ->  Bitmap Index Scan on z_heizung_idx_ts_f  (cost=0.00..5.62 rows=437 width=0) (actual time=0.463..0.463 rows=1336 loops=1)
                     Index Cond: ((myextract(time_string) > 1301007600::double precision) AND (myextract(time_string) < 1301093999::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.011..0.015 rows=1 loops=1336)
               Index Cond: (d.entry_id = "outer".id)
               Filter: (sensor_id = 1)
 Total runtime: 30.547 ms
(12 rows)

Das Laden der Webseite hat sich von 1,6 Sek. auf 0,5 Sek. verringert.
Wollte damit eigentlich nochmals Danke sagen,
Christian

Hmm, interessant, dass die Version mit to_timestamp() langsamer ist als mit dem Literal. Warum der Umweg über eine eigene Funktion nochmal schneller ist kann ich nicht erklären, das macht eigentlich keinen Sinn.
Von 50ms auf 30ms könnte aber auch ein Caching Effekt sein.