Mit enable_seqscan=0 kannst du sequentielle Scans nicht komplett ausschalten, nur alle anderen Methoden bevorzugen. Wenn nichts mehr übrig bleibt, was genutzt werden könnte, fällt PostgreSQL trotzdem auf einen sequentiellen Scan zurück - wie sonst sollten Daten geliefert werden können.
Und immer noch bezweifle ich, ob das später auch deinen realen Testdaten und der Vorgehensweise der Applikation entspricht, aber langsam habe ich genug davon, mich zu wiederholen.
test=# \d forum
Table "public.forum"
Column | Type | Modifiers
--------+---------+-----------
i | integer | not null
t | text |
Indexes:
"forum_pkey" PRIMARY KEY, btree (i)
"idx_5" btree ("substring"(t, 6, 100) text_pattern_ops)
"idx_hash" btree (lower(md5(t)) text_pattern_ops)
test=# explain analyse select t from forum where lower(md5(t)) like 'f02a%';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on forum (cost=65.01..11392.55 rows=5000 width=14) (actual time=0.092..0.255 rows=17 loops=1)
Filter: (lower(md5(t)) ~~ 'f02a%'::text)
-> Bitmap Index Scan on idx_hash (cost=0.00..65.01 rows=5000 width=0) (actual time=0.056..0.056 rows=17 loops=1)
Index Cond: ((lower(md5(t)) ~>=~ 'f02a'::text) AND (lower(md5(t)) ~<~ 'f02b'::text))
Total runtime: 0.357 ms
(5 rows)
test=*# select t from forum where lower(md5(t)) like 'f02a%';
t
-------------
test_32407
test_114293
test_167170
test_206604
test_246075
test_360579
test_377093
test_441153
test_562482
test_569935
test_622616
test_633326
test_653142
test_751118
test_862279
test_929618
test_970509
(17 rows)
test=*# analyse;
ANALYZE
test=*# explain analyse select t from forum where lower(md5(t)) like 'f02a%';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on forum (cost=2.09..57.17 rows=14 width=14) (actual time=0.086..0.192 rows=17 loops=1)
Filter: (lower(md5(t)) ~~ 'f02a%'::text)
-> Bitmap Index Scan on idx_hash (cost=0.00..2.09 rows=14 width=0) (actual time=0.045..0.045 rows=17 loops=1)
Index Cond: ((lower(md5(t)) ~>=~ 'f02a'::text) AND (lower(md5(t)) ~<~ 'f02b'::text))
Total runtime: 0.289 ms
(5 rows)
test=*#
myDB=# explain analyse select
myDB-# T."textkey"
myDB-# from myDB."TX_TEXTKEYS" As T
myDB-# where LOWER(T."textkey") [color=Blue][B]>=[/B][/color] 'f02a%'
myDB-# ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on "TX_TEXTKEYS" t (cost=1292.70..25558.46 rows=23739 width=70) (actual time=55.826..262.766 rows=61767 loops=1)
Recheck Cond: (lower((textkey)::text) >= 'f02a%'::text)
-> Bitmap Index Scan on idx_textkey (cost=0.00..1286.76 rows=23739 width=0) (actual time=49.386..49.386 rows=61767 loops=1)
Index Cond: (lower((textkey)::text) >= 'f02a%'::text)
Total runtime: [color=Blue]303.985 ms - find ich angemessen .. [/color]
(5 Zeilen)
Nochmal zum Vergleich:
myDB=# explain analyse select
myDB-# T."textkey"
myDB-# from archiv9."TX_TEXTKEYS" As T
myDB-# where LOWER(T."textkey") [B][color=Blue]like [/color][/B]'f02a%';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Seq Scan on "TX_TEXTKEYS" t (cost=100000000.00..100038039.06 rows=1 width=70) (actual time=81.655..3318.712 rows=10 loops=1)
Filter: (lower((textkey)::text) ~~ 'f02a%'::text)
Total runtime: 3318.771 ms :mad:
(3 Zeilen)
das hab ich auch verstanden ich wollte nur darauf hinweisen, dass er ploetzlich der meinung ist keinen brauchbaren index zu kennen …
Und immer noch bezweifle ich, ob das später auch deinen realen Testdaten und der Vorgehensweise der Applikation entspricht, aber langsam habe ich genug davon, mich zu wiederholen.
das ist sehr schade … erst war alles viel zu gleich: “Test_%” und nun ist alles zu durcheinander? Wie sollten reelle Daten denn aussehen? Jetzt habe ich hashs genommen die man auch gut sortieren und suchen kann. Also realer koennen sie fast nicht sein - nur unguenstiger … ich bezweifle dass die Index-Implementierungen wert darauf legen, ob die varchar spalten deutsche, englische oder chinesische woerter enthalten.
Du musst ja nicht am Problem bleiben … vielleicht stellt sich am Ende heraus dass es bei mir liegt - vielleicht aber auch nicht.
ich hab nochmal versucht die Unterschiede zwischen dem was du da Triebst und dem was ich hier Frickle zu finden … erst dachte ich der Datentyp - Text … der hat aber nix gebracht … und dann stieß ich wieder auf text_pattern_ops - das mir wohl bei dem ganzen Index droppen und createn abhanden gekommen ist. Sehrwohl einen PG-Eigenheit an die ich mich aber gewöhnen werde.
Brauch ich die generell ? Oder nur bei solchen exoten wie berechneten Indices? - Muss sie ohnehin gleich mal studieren …
myDB=# explain analyse select
myDB-# T."textkey"
myDB-# from myDB."TX_TEXTKEYS" As T
myDB-# where LOWER(T."textkey") like 'f02a%'
myDB-# ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_textkey on "TX_TEXTKEYS" t (cost=0.01..8.74 rows=1 width=70) (actual time=0.042..0.103 rows=10 loops=1)
Index Cond: ((lower((textkey)::text) ~>=~ 'f02a'::text) AND (lower((textkey)::text) ~<~ 'f02b'::text))
Filter: (lower((textkey)::text) ~~ 'f02a%'::text)
Total runtime:[color=Lime] [color=SeaGreen]0.148 ms :D [/color][/color]
(4 Zeilen)
Nunja … sehr tief gehen sie dort ja nicht … für die normale Nutzung der DB scheint es ohne kaum relevant zu sein. Fuer indices auf Datentypen scheint es dazu auch nicht viel zu sagen zu geben … neue Datentypen habe ich auch nicht vor zu etablieren …
irgendjemand hat wohl vergessen für diese zu 99.9999% vorkommenden einfachen Indices geeignete Default Operator-Klassen festzulegen bzw. zu konfigurieren
Vielleicht ist dieses Operator-Klassen Konzept auch total falsch, weil man mich nicht gefragt hat
Ich möchte Dich hiermit ganz offiziell bitten, passende Patches dem PG-Entwicklerteam vorzuschlagen. Nörgeln ist das eine, konstruktiv helfen das andere. Das eine kannst Du, kannst Du auch das andere?