Index nicht verwendet

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.

Ich biete 0,3 ms:

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=*#

Andreas

Oh Wunder:

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)

Gruß & Dank
Steffen

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.

Gruß
Steffen

Danke danke für Deine Gedult,

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)

Gruß & Dank
Steffen

Dann beginne Dein Studium hier: PostgreSQL: Documentation: Manuals: PostgreSQL 8.3: Operator Classes and Operator Families

Andreas

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 :rolleyes:

Vielleicht ist dieses Operator-Klassen Konzept auch total falsch, weil man mich nicht gefragt hat :smiley:

Nun denn -

Herzlichsten Dank für die Mühen
Steffen

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?

Andreas, EOD.