Abfrage dauert zu lange

[long_forum]

Habe jetzt auf meine 4 Spalten einen Index gesetzt:

'CREATE INDEX idx_SystemEvents_FromHost ON SystemEvents (FromHost);'
'CREATE INDEX idx_SystemEvents_SysLogTag ON SystemEvents (SysLogTag);'
'CREATE INDEX idx_SystemEvents_DeviceReportedTime ON SystemEvents (DeviceReportedTime;'
'CREATE INDEX idx_SystemEvents_Message ON SystemEvents (Message);'

Aber entweder benutzt er den Index und findet nichts oder er benutzt nicht den Index und findet Ergebnisse.

"EXPLAIN ANALYSE SELECT * FROM SystemEvents WHERE FromHOST = '192.168.1%';"
                                                                QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_systemevents_fromhost on systemevents  (cost=0.43..8.45 rows=1 width=850) (actual time=0.056..0.056 rows=0 loops=1)
   Index Cond: ((fromhost)::text = '192.168.10%'::text)
 Planning time: 1.575 ms
 Execution time: 0.132 ms
(4 rows)


"EXPLAIN ANALYSE SELECT * FROM SystemEvents WHERE FromHOST like '192.168.1%';"
                                                        QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------------
 Seq Scan on systemevents  (cost=0.00..62157.71 rows=1349689 width=850) (actual time=0.040..639.733 rows=1350411 loops=1)
   Filter: ((fromhost)::text ~~ '192.168.10%'::text)
   Rows Removed by Filter: 6591
 Planning time: 1.538 ms
 Execution time: 705.146 ms
(5 rows)


"EXPLAIN ANALYSE SELECT * FROM SystemEvents WHERE FromHOST like '192.168.100.7%';"
                                                        QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------------
 Seq Scan on systemevents  (cost=0.00..62163.23 rows=1349086 width=850) (actual time=0.051..646.742 rows=1349691 loops=1)
   Filter: ((fromhost)::text ~~ '192.168.100.7%'::text)
   Rows Removed by Filter: 7423
 Planning time: 1.916 ms
 Execution time: 713.701 ms
(5 rows)

Wie muss ich denn den Index anwenden (like oder =, mit oder ohne %, oder andere Platzhalter)?



Kann ich auf meine nachfolgenden Spalten bessere Indexe legen als meine bisher gesetzten Indexe (z.B. text_pattern_ops usw.)? Was würde hier Sinn machen?

DeviceReportedTime timestamp without time zone NULL
FromHost varchar(60) NULL
Message text
SysLogTag varchar(60)

 

[akretschmer]

Aber entweder benutzt er den Index und findet nichts oder er benutzt nicht den Index und findet Ergebnisse.

Das ist quatsch.

test=*# select '192.168.10.10' = '192.168.1%';
 ?column?
----------
 f
(1 row)

test=*# select '192.168.10.10' like '192.168.1%';
 ?column?
----------
 t
(1 row)

test=*#
[/quote]

selbe Daten im Vergleich, aber unterschiedliche Ergebnisse, weil: unterschiedliche Operatoren. Ob da ein Index drauf ist oder nicht ändert nichts am Resultat einer Abfrage.

[quote]
The operator classes text_pattern_ops, varchar_pattern_ops, and bpchar_pattern_ops support B-tree indexes on the types text, varchar, and char respectively. The difference from the default operator classes is that the values are compared strictly character by character rather than according to the locale-specific collation rules. This makes these operator classes suitable for use by queries involving pattern matching expressions (LIKE or POSIX regular expressions) when the database does not use the standard "C" locale.
[/quote]

Für Deine IP-Geschichten wäre evtl. der Einsatz passenderer Datentypen sinnvoll:

[list]
[*] https://www.postgresql.org/docs/12/datatype-net-types.html
[*] https://www.postgresql.org/docs/12/functions-net.html
[/list]

 

[long_forum]

Aber warum benutzt er in meinem zweiten Beispiel den Index nicht?

 

[akretschmer]

weil er mit

rows=1349689

rechnet, und tatsächlich kommen auch 1350411. Da ist ein Seq-Scan günstiger als wenn er erst den Index und dann noch mal quasi die ganze Tabelle lesen muß. Kostenmodell von PG ;-)

 

[long_forum]

ASO...da hatte ich ja testen können bis zum Umfallen.