Performance Problem bei Abfrage

Es geht um einen Artikelstamm mit ~50000 Artikeln.
Es kann mehrere Artikel mit der gleichen Nummer geben, davon ist aber immer nur einer aktuell. (Deswegen das gueltig_ab)

Das Ganze soll für den Benutzer dynamisch laufen.
Pro eingebener Ziffer nähert sich das Ergebnis an die Artikelnummer an und gibt 11 Einträge zurück.

So sehen die Abfragen aus wenn der Benutzer 12 eintippt

select distinct SUBSTRING(artnr,1,2)
from stammdaten.artikelstamm as a where gueltig_ab = (select gueltig_ab from stammdaten.artikelstamm
where artnr = a.artnr and artnr_zusatz = a.artnr_zusatz order by gueltig_ab desc limit 1) and artnr like ‘1%’ limit 11

select distinct SUBSTRING(artnr,1,3)
from stammdaten.artikelstamm as a where gueltig_ab = (select gueltig_ab from stammdaten.artikelstamm
where artnr = a.artnr and artnr_zusatz = a.artnr_zusatz order by gueltig_ab desc limit 1) and artnr like ‘12%’ limit 11

allerdings dauert es ~5 min bis pg mir ein Ergebnis liefert

Was spräche dagegen, ein extra Feld ‘gueltig’ als BOOL zu machen und einen uniqe Index zu machen?

test=*# \d artikel
      Table "public.artikel"
   Column    |  Type   | Modifiers
-------------+---------+-----------
 nr          | text    |
 bezeichnung | text    |
 gueltig_ab  | date    |
 gueltig     | boolean |

test=*# create unique index idx_art on artikel(nr, gueltig) where gueltig = 't'::bool;
CREATE INDEX
test=*# commit;
COMMIT
test=# select * from artikel ;
 nr | bezeichnung | gueltig_ab | gueltig
----+-------------+------------+---------
(0 rows)

test=*# insert into artikel values ('1000', 'art1', '2007-01-01'::date, 'f');
INSERT 0 1
test=*# insert into artikel values ('1000', 'art1', '2007-02-01'::date, 'f');
INSERT 0 1
test=*# insert into artikel values ('1000', 'art1', '2007-03-01'::date, 'f');
INSERT 0 1
test=*# insert into artikel values ('1000', 'art1', '2007-04-01'::date, 't');
INSERT 0 1
test=*# insert into artikel values ('1000', 'art1', '2007-05-01'::date, 't');
ERROR:  duplicate key violates unique constraint "idx_art"
test=!#

Damit ist immer nur einer gültig, und Dein Select kann dieses gueltig-Feld nutzen.

So sehen die Abfragen aus wenn der Benutzer 12 eintippt

select distinct SUBSTRING(artnr,1,2)
from stammdaten.artikelstamm as a where gueltig_ab = (select gueltig_ab from stammdaten.artikelstamm
where artnr = a.artnr and artnr_zusatz = a.artnr_zusatz order by gueltig_ab desc limit 1) and artnr like ‘1%’ limit 11

Stringvergleiche sind teuer, vermutlich hast Du auch keinen Index. Was passiert, wenn ein Artikel am selben Tag mehrfach geändert wird?


allerdings dauert es ~5 min bis pg mir ein Ergebnis liefert

Zeige uns das explain analyse.

Andreas

Das Explain analyze:

“Limit (cost=3868758.83…3868758.84 rows=1 width=58) (actual time=618058.256…618067.260 rows=10 loops=1)”
" -> Unique (cost=3868758.83…3868758.84 rows=1 width=58) (actual time=618058.254…618067.245 rows=10 loops=1)"
" -> Sort (cost=3868758.83…3868758.83 rows=3 width=58) (actual time=618058.251…618062.345 rows=8483 loops=1)"
" Sort Key: “substring”((artnr)::text, 1, 2)"
" -> Index Scan using artikelstamm_index on artikelstamm a (cost=0.00…3868758.80 rows=3 width=58) (actual time=92.304…618015.465 rows=8483 loops=1)"
" Index Cond: (((artnr)::text >= ‘1’::character varying) AND ((artnr)::text < ‘2’::character varying))"
" Filter: (((artnr)::text ~~ ‘1%’::text) AND (gueltig_ab = (subplan)))"
" SubPlan"
" -> Limit (cost=6517.52…6517.53 rows=1 width=8) (actual time=72.822…72.824 rows=1 loops=8483)"
" -> Sort (cost=6517.52…6517.53 rows=3 width=8) (actual time=72.817…72.817 rows=1 loops=8483)"
" Sort Key: gueltig_ab"
" -> Seq Scan on artikelstamm (cost=0.00…6517.50 rows=3 width=8) (actual time=37.929…72.791 rows=1 loops=8483)"
" Filter: (((artnr)::text = ($0)::text) AND ((artnr_zusatz)::text = ($1)::text))"
“Total runtime: 618067.465 ms”


Das mit dem boolean könnte ich wohl wahrscheinlich so machen. Zumindest für die jetzigen Anforderungen :slight_smile:
Wenn es aber eine Möglichkeit gibt meine Abfrage so zu verändern das es nur noch ~1-2 sec dauert wäre das noch besser.

Schon mal vielen Dank für die Hilfe!

Die Zeit steckt in dem inneren Select, welches 8484 mal durchläuft und daher teuer ist. Stecke mal die Bedingung auf artnr mit in das subselect.

Andreas