Index nicht verwendet

hallo,

es wird so sein wie du vermutest

habs deine Anweisungen trotzdem mal befolgt …

myDB=# \d myDB."TX_TEXTKEYS";
                                    Tabelle ╗myDB.TX_TEXTKEYS½
 Spalte  |          Typ           |                               Attribute
---------+------------------------+------------------------------------------------------------------------
 id_tky  | integer                | not null default nextval('myDB."TX_TEXTKEYS_id_txt_seq"'::regclass)
 textkey | character varying(255) | not null
Indexe:
    ╗id_tky½ PRIMARY KEY, btree (id_tky)
    ╗idx_textkey½ UNIQUE, btree (lower(textkey::text))

habe leider auf die Schnelle nichts zum Thema Indexgröße hinter deinem Link gefunden.

Aber zum Thema - ja - der Index ist logischerweise so groß wie die Daten selbst - vorrausgesetzt, dass die Index-Breite der Breite der Daten entspricht - davon gehe ich mal aus. Ich hatte nur gedacht, dass es sich bei diesem Select durchaus lohnen müsste, den Index zu benutzen, da man ja vergleichsweise schnell den “Startwert” / die “Range” für den angegebenen Filter adressieren kann. Das kommt aber wie gesagt auf die Implementierung des “Index-Scans” an - da fehlt mir speziell zu Postgres Hintergrundwissen. Ich brauche an dieser Stelle einen Unique Index - weshalb mir PG die anderen Index-Typen verweigert (hash etc.)

Gruss
Steffen

Dann kommt wieder mein Beispiel von weiter oben zum Tragen: wenn der Index und die Tabelle gelesen werden muss, lohnt sich das nicht.

Dein Problem ist effektiv: dein Index ist zu breit, wahrscheinlich breiter, als du ihn benötigst. Selbst wenn der Index einen btree aufbaut: durch deinen gleichen Anfangstext verteilen sich deine Werte trotzdem über den ganzen Baum und damit über den ganzen Index.

Kommen wir zurück zu einem anderen Hinweis: verwende reale Daten. Vielleicht klappt das dann besser :wink:

Frage 1 kann man die Erzeugung des BTREE beeinflussen ? Das zu probieren - dafuer waeren diese Daten wieder gut - Sicher ist der Anfangswert ueberall ‘Test_’ der Filter ist aber breiter … und fuellt die Datenbreite fast vollstaendig aus - weshalb der Planer auch nur 1 Zeile erwartet.
Frage 2 wenn ich im Model die Feldbreite aendere (via PGAdmin) - hab ich heute mal gemacht von 255 auf 80 - wird dann der Index auch neu aufgebaut ? oder muss ich dann ein REINDEX machen - uebrigends hat ein REINDEX allein schon dafuer gesorgt, dass der index-scan eine halbe sec. (25%) schneller wurde, aber das war allerdings immernoch “langsamer” als der geschaetzte Seq.-scan.

Gruß
Steffen

Zu Frage 1: PostgreSQL kann aber nicht “mittendrin” suchen. Sprich, wenn dein Index Wert nach dem immer gleichen Startwert unterschiedlich ist, bringt das gar nichts. Dafür ist ein Index auch nicht gedacht.

Zu Frage 2: kann ich auswendig leider nicht beantworten, aber sollte eigentlich so sein. Es sollten keine Werte (255 Zeichen breit) im Index stehen, wenn die Tabelle nur 80 Zeichen Breite hat.

  1. wie ein Index im Prinzip funktioniert weiss ich … ich meinte aber, ob man den aufbau des BTREE beeinflussen kann - nicht um zwischen drin zu suchen, sondern um den BTREE entsprechend den Daten optimieren zu koennen.

Ich habe gerade mal die Breite meines Index verkleinert:

CREATE UNIQUE INDEX idx_textkey
  ON archiv9."TX_TEXTKEYS"
  USING btree
  (lower(substring(textkey::text from 1 for 48)));

brachte aber nix - 1. weil 48 immer noch breiter ist als die 14 die der Planer fuer die Datenbreite schaetzt und 2. weil der Planer auch mit enable_seqscan=0 nichts mit diesem Index anfangen kann. Also so schlau is der Planer auch nicht.

myDB=# set enable_seqscan=0;
SET
myDB=# explain analyse select
myDB-#       T."textkey"
myDB-# from myDB."TX_TEXTKEYS" As T
myDB-# where LOWER(T."textkey") LIKE LOWER('test^_12123%') ESCAPE '^'
myDB-# order by LOWER(T."textkey") desc;
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=100021688.75..100021701.25 rows=5000 width=14) (actual time=1489.390..1489.397 rows=11 loops=1)
   Sort Key: lower((textkey)::text)
   ->  Seq Scan on "TX_TEXTKEYS" t  (cost=100000000.00..100021381.56 rows=5000 width=14) (actual time=17.633..1489.331 rows=11 loops=1)
         Filter: (lower((textkey)::text) ~~ 'test\\_12123%'::text)
 Total runtime: 1489.444 ms
(5 Zeilen)

Gruß

Du hast analyse vergessen…

rows=5000 → rows11

Andreas

Du kannst dir einen komplett eigenen Indextyp schreiben mittels GIN/GIST, der ganz auf deine Bedürfnisse eingehen kann. Dabei kannst du dann z.B. überall das “test” am Anfang wegschneiden und nur den Rest indizieren.

Ich frage mich aber, wieso du weiter auf diesem Problem herumarbeitest, das sich ganz offensichtlich durch falsche Testdaten stellt. Ein Testlauf mit realen Daten wird dir ganz andere Ergebnisse liefern.

Was hälst denn davon:

myDB=# [color=Blue][U]analyse myDB."TX_TEXTKEYS"[/U][/color];
ANALYZE
myDB=# explain analyse select
myDB-#       T."textkey"
myDB-# from myDB."TX_TEXTKEYS" As T
myDB-# where LOWER(T."textkey") LIKE LOWER('test^_12123%') ESCAPE '^'
myDB-# order by LOWER(T."textkey") desc;
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=21688.29..21700.79 [U][color=Blue]rows=5000[/color][/U] width=14) (actual time=1457.286..1457.294 rows=11 loops=1)
   Sort Key: lower((textkey)::text)
   ->  Seq Scan on "TX_TEXTKEYS" t  (cost=0.00..21381.10 rows=5000 width=14) (actual time=18.284..1457.099 rows=11 loops=1)
         Filter: (lower((textkey)::text) ~~ 'test\\_12123%'::text)
 Total runtime: 1457.342 ms
(5 Zeilen)

Hier nochmal die Indices auf der Table:

myDB=# \d myDB."TX_TEXTKEYS";
                                    Tabelle ╗myDB.TX_TEXTKEYS½
 Spalte  |          Typ           |                               Attribute
---------+------------------------+------------------------------------------------------------------------
 id_tky  | integer                | not null default nextval('myDB."TX_TEXTKEYS_id_txt_seq"'::regclass)
 textkey | character varying(255) | not null
Indexe:
    ╗id_tky½ PRIMARY KEY, btree (id_tky)
    ╗idx_textkey½ UNIQUE, btree (lower("substring"(textkey::text, 1, 48)))

myDB=#

hmm … ich kann analyse’n wie ich will - 's will gerade nimmer

:confused:

Steffen

ich bin … ratlos.

Andreas

ich dachte dieses Problem interessiert dich noch … ich reite nicht drauf rum. Sicher gibt es günstigere Daten fuer dieses Problem - jedoch sieht die Realitaet wie folgt aus:

die realen Daten werden durchaus sehr haeufig vorkommende “Präfixe” haben … ähnlich wie dieses Test_ … vielleicht gibt es dann 3 od. 4 verschiedene mit ähnlicher Breite … welchen Einfluss hat das auf den BTREE ? → ich weiss es nicht …

Soll ich drauf vertrauen, dass es schon irgenwie klappt ? Die realen Daten dann schon “so wenige” sein werden, dass es faktisch egal ist ob er nen Index nimmt oder nicht ? Bei 5000 Datensaetzen ist dir das wahrscheinlich “wurscht” :slight_smile: … mir aber nicht …

Ich möchte eben abschätzen können wo es Probleme geben wird.
Ich habe Postgres nicht gewählt weil ich ein Fan davon bin. Das DBMS ist mir persönlich egal - haben alle ihre Sonnen und Schattenseiten. Die Kundschaft möchte nunmal Postgres - dann kriegt sie Postgres.

Ich dachte bisher immer, dass DBMS mit Daten umgehen koennen sollten und nicht die Daten an die DBMS angepasst werden müssen. :wink: (muss nab leider ab und an mal machen) Und zweifels ohne sind diese naiven Testdaten so “durchschaubar”, dass man mir doch schon erlaubt sein sollte etwas enttaeuscht zu sein. Nicht weil er keinen Index nimmt - sondern einfach weils PG nicht wirklich so souverän damit umgeht wie ich erhoffte. Sicherlich scheinen 1.5 sec Latenz für eine Mio Datensaetze nicht viel - für die Popeldaten und diese einfache Query ist das aber irgendwie nicht akzeptabel - und ich versuche jetzt fuer genau dieses Popel-Problem eine Lösung zu finden - um was zu lernen - vielleicht bleibt die Query mit diesen Daten auch so lahm vielleicht aber auch nicht.

Bisher habt ihr / hast du mir wirklich sehr geholfen - hab einiges erfahren über PG und seinen Planer - wenn dich das langweilt wärs schade drum.

Nochmal - ich will PG garnicht schlecht machen - ich weiss dass ITler / Entwickler sehr eitel sein koenne - darum gehts aber gerade ueberhaupt garnicht.

Gruß & Dank
Steffen

Du kannst nicht erwarten, das eine Datenbank von Haus aus all das macht, was du von ihr erwartest. Weder bei PostgreSQL noch bei Oracle noch sonst wo.

Speziell wenn du mit “schwierigen” Datenkonstellationen oder komplizierten Suchen ankommst, wird jede Datenbank einbrechen. Jede Suche ist darauf spezialisiert, kleine Datenbestände in großen Mengen zu finden. Große Ergebnismengen in großen Datenbeständen sind nicht das Ziel einer Datenbank - das gibt immer Probleme.

Wenn du auch mit deinen realen Daten in derartige Probleme läufst, wird dir nichts übrig bleiben, als mehr Zeit in das Problem zu investieren und genau hinzuschauen. Aber irgendwann ist auch ein Punkt erreicht, an dem wir hier mehr herumraten und rätseln, als dir helfen zu können. Nachdem wir passende Fragen gestellt haben, kamen von dir auch die Ergebnisse, wie wiederum geholfen haben, einige deiner Fragen zu beantworten und unklares richtig zu stellen. Das können wir hier noch einige Forenseiten weiter betreiben, aber je mehr das ganze ins Detail geht, desto aufwendiger, mühseliger, zeitraubender und unergiebiger wird das.

Um es kurz zu machen: wenn ihr ein Produkt mit PostgreSQL als Backend anbieten möchtet, solltet ihr entsprechende Erfahrung in-house haben. Alles andere wäre … zumindest unvorteilhaft.

Präfix abschneiden?

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)

test=# explain select t from forum where substring(t,6,100) like '123%';
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on forum  (cost=10.71..2718.87 rows=951 width=14)
   Filter: ("substring"(t, 6, 100) ~~ '123%'::text)
   ->  Bitmap Index Scan on idx_5  (cost=0.00..10.71 rows=951 width=0)
         Index Cond: (("substring"(t, 6, 100) ~>=~ '123'::text) AND ("substring"(t, 6, 100) ~<~ '124'::text))
(4 rows)

Aber ads hat durchaus recht, verwende reale Daten.


Andreas

also ich habe dieses substring aus dem index nocheinmal verbannt … und siehe da … rows=1 …

offenbar kommt analyse nicht mit komplizierteren expressions klar - dann nimmt er wieder default werte an … hm

interessant auch was PG aus meinem folgenden statement erzeugt

CREATE UNIQUE INDEX idx_textkey
  ON archiv9."TX_TEXTKEYS"
  USING btree
  (lower(substring(textkey::text from 1 for 48)));

daraus wird:

myDB=# \d myDB."TX_TEXTKEYS";
                                    Tabelle myDB.TX_TEXTKEYS½
 Spalte  |          Typ           |                               Attribute
---------+------------------------+------------------------------------------------------------------------
 id_tky  | integer                | not null default nextval('myDB."TX_TEXTKEYS_id_txt_seq"'::regclass)
 textkey | character varying(255) | not null
Indexe:
    ╗id_tky½ PRIMARY KEY, btree (id_tky)
    ╗idx_textkey½ UNIQUE, btree (lower([color=Magenta]"substring"[/color](textkey::text, 1, 48)))

koennte auch sein, dass das korrekt ist - dass er das quotet …

in der Doku steht zu solchen “berechneten” Indices auch nur irgendwas von einer einzigen Funktion - zwar nicht explizit - aber vielleicht geht sieht man “mehr” einfach nicht als sinnvoll an …

Zurück zum Problem “zu breiter Index” … wie wuerdest du denn die Index-breite begrenzen … auch so wie oben?

Gruß & Dank
Steffen

das erwarte ich auch nicht

Speziell wenn du mit “schwierigen” Datenkonstellationen…

Dieses problem ist nicht schwierig - es wird eine kleine Datenmenge aus einer grossen erwartet - wie gewohnt … 11 aus 1 Mio. und darauf muss ich ca 1.5 - 2 sec warten,

Wenn du auch mit deinen realen Daten in derartige Probleme läufst, …

Andere Tabellen → Andere Probleme → wie ich schon sagte … nach einiger Überlegung muss ich feststellen, dass diese Test-daten garnicht so falsch sind … nicht um euch zu quaelen …

Um es kurz zu machen: wenn ihr ein Produkt mit PostgreSQL als Backend anbieten möchtet, solltet ihr entsprechende Erfahrung in-house haben. …

  1. bin ich gerade dabei mit entsprechende Erfahrungen zu erarbeiten - in SQL und rel. Problemen im Allgemeinen bin ich äußerst fit. Nur PG ist etwas neu fuer mich - aber wenn sie sich SQL-DB schimpft, dann darf ich ja wohl auch einige Erwartungen erfüllt sehen. Es ist ja nicht gerade so, dass garnichts ginge.
  2. das Produkt soll nicht nur PG koennen … es sollen verschiedene Backends bedient werden - das ist aber ein anderes Thema :slight_smile:

Ich biete 1111 aus einer Million in 6,5 Millisekunden:

test=*# select count(1) from forum;
  count
---------
 1000000
(1 row)

test=*# explain analyse select t from forum where substring(t,6,100) like '123%';
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on forum  (cost=10.71..2718.87 rows=951 width=14) (actual time=0.712..4.263 rows=1111 loops=1)
   Filter: ("substring"(t, 6, 100) ~~ '123%'::text)
   ->  Bitmap Index Scan on idx_5  (cost=0.00..10.71 rows=951 width=0) (actual time=0.654..0.654 rows=1111 loops=1)
         Index Cond: (("substring"(t, 6, 100) ~>=~ '123'::text) AND ("substring"(t, 6, 100) ~<~ '124'::text))
 Total runtime: 6.507 ms
(5 rows)



Andere Tabellen → Andere Probleme → wie ich schon sagte … nach einiger Überlegung muss ich feststellen, dass diese Test-daten garnicht so falsch sind … nicht um euch zu quaelen …

\

  1. bin ich gerade dabei mit entsprechende Erfahrungen zu erarbeiten - in SQL und rel. Problemen im Allgemeinen bin ich äußerst fit. Nur PG ist etwas neu fuer mich - aber wenn sie sich SQL-DB schimpft, dann darf ich ja wohl auch einige Erwartungen erfüllt sehen. Es ist ja nicht gerade so, dass garnichts ginge.

Ich sagte wohl schon, daß Textvergleiche nun mal schwierig sind. Vielleicht solltest Du das prinzipiell überdenken.

  1. das Produkt soll nicht nur PG koennen … es sollen verschiedene Backends bedient werden - das ist aber ein anderes Thema > :slight_smile: >

Ja. Die Tatsache, daß Textvergleiche vergleichsweise teuer sind, bleibt aber bestehen.

Andreas

Ich biete 1111 aus einer Million in 6,5 Millisekunden:

Das ist schön für Dich :slight_smile:

Ich fuelle meine Tabelle gerade mal mit MD5 hashs + ursprungsdaten und schau mal was dann passiert … sollten doch schöne Daten für einen Index sein …

Ja, aber es wird Dir nur schwer gelingen, eine Mustersuche a la ‘test_123%’ gegen md5-Hash zu fahren…

Andreas

Wohingegen die Suche nach einem spezifischen md5 Hash direkt aus dem Index abgearbeitet werden müsste … aber damit hat man halt maximal einen Treffer (ausser bei Hash Kollisionen).

das ist auch garnicht meine intension … ich versuche zu testen wie der index / planer wirkt - bei anderen Daten … natuerlich kann ich dann nicht nach like test_% suchen … ich suche dann nach % …

its analysed :

myDB=# analyse myDB."TX_TEXTKEYS";
ANALYZE

so sehen die daten aus :

myDB=# select
myDB-#       T."textkey"
myDB-# from myDB."TX_TEXTKEYS" As T
myDB-# where LOWER(T."textkey") LIKE LOWER('f02a%') ESCAPE '^';
                               textkey
----------------------------------------------------------------------
 f02a41351aeb5fd5e4ef2f5e27cb3eff10012859636Test_4910012859636Test_49
 f02a53e3beebd71d37c43e1fa716ada810285562363Test_5210285562363Test_52
 f02a67326557fb4dd9959b543313c67910685356361Test_5610685356361Test_56
 f02addd605c09132125bfaaf7846204812098480493Test_7012098480493Test_70
 f02ac45d2429c32a516fa64c08ac9e5412338342891Test_7212338342891Test_72
 f02a029b4ff5347d5e130006c5a7ab1512668106189Test_7612668106189Test_76
 f02ae5d209559c73a780e46b7f6b899d6552945037Test_156552945037Test_15
 f02a66fff0c12b8048ab610f852f3ebc6630515813Test_156630515813Test_15
 f02a4edd6e2d81ef0654a4bdd159dc756665866166Test_166665866166Test_16
 f02ada6d9d7adc9b51bd64fc7f68f3768539814906Test_348539814906Test_34
(10 Zeilen)

so sieht der Index aus:

myDB=# \d myDB."TX_TEXTKEYS";
                                    Tabelle ╗myDB.TX_TEXTKEYS½
 Spalte  |          Typ           |                               Attribute
---------+------------------------+------------------------------------------------------------------------
 id_tky  | integer                | not null default nextval('myDB."TX_TEXTKEYS_id_txt_seq"'::regclass)
 textkey | character varying(255) | not null
Indexe:
    ╗id_tky½ PRIMARY KEY, btree (id_tky)
    ╗idx_textkey½ UNIQUE, btree (lower(textkey::text))

das sagt der Planer :astonished: :confused: :angry:

[color=Red]myDB=# set enable_seqscan=0;[/color]
SET
myDB=# explain analyse select
myDB-#       T."textkey"
myDB-# from myDB."TX_TEXTKEYS" As T
myDB-# where LOWER(T."textkey") LIKE LOWER('f02a%') ESCAPE '^';
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on "TX_TEXTKEYS" t  (cost=100000000.00..100037854.00 rows=1 width=70) (actual time=82.217..3327.394 rows=10 loops=1)
   Filter: (lower((textkey)::text) ~~ 'f02a%'::text)
 Total runtime: 3327.449 ms
(3 Zeilen)

und das sage ich:

hmmm ...