Query Autocomplete mit Sortierung nach Vorkommen in anderer Tabelle

Fragen und Probleme rund um die Abfragespache SQL.
Antworten
Ciatronical
Benutzer
Beiträge: 53
Registriert: Sa 21. Aug 2010, 13:10

Query Autocomplete mit Sortierung nach Vorkommen in anderer Tabelle

Beitrag von Ciatronical » Fr 19. Jan 2018, 17:10

Hey All,

ich habe zwei Tabellen.
In parts stehen die Teile und Dienstleistungen. Diese sollen für eine Autovervollständigung verwendet werden.
In orderitems befinden sich die in den Rechnungen verwendeten Positionen. Dort sind nicht alle in parts befindlichen Einträge vorhanden.
(Es gibt Teile die noch nie verkauft wurden.)

Die folgende Abfrage sortiert die in parts angefragten Teile nach Häufigkeit der Verwendung in den Rechnungen.

Teile die oft verkauft werden, sollen in der Autocomplete oben stehen dass man nicht so oft "Pfleil nach unten Taste" tippen muss.

Code: Alles auswählen

SELECT description, partnumber, id FROM parts WHERE description ILIKE '%innen%' ORDER BY ( SELECT COALESCE( NULLIF( ( SELECT ct FROM ( SELECT qty, count( qty ) AS ct FROM orderitems WHERE parts_id = parts.id GROUP BY 1 ORDER BY ct DESC LIMIT 1 ) AS ct ) , 0 ), 0 ) ) DESC;
Diese Abfrage ist jedoch ziemlich langsam (314ms).

Wie kann ich diese Abfrage tunen / schneller machen??

Viele Grüße Ronny

akretschmer
Erfahrener Benutzer
Beiträge: 7404
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Query Autocomplete mit Sortierung nach Vorkommen in anderer Tabelle

Beitrag von akretschmer » Fr 19. Jan 2018, 17:54

wie lange dauert es ohne Sortierung? Wo steckt die Zeit, im SELECT oder erst in der Sortierung? Was sagt xplain analyse dazu?

Evtl. suchst Du einen funktionalen Index:

Code: Alles auswählen

test=# create table ronny(id serial primary key, t text);
CREATE TABLE
test=*# insert into ronny (t) select md5(random()::text) from generate_series(1, 100000) s;
INSERT 0 100000
test=*# explain analyse select * from ronny where t ilike '%abc%';
                                                QUERY PLAN                                                
----------------------------------------------------------------------------------------------------------
 Seq Scan on ronny  (cost=0.00..2157.98 rows=4237 width=36) (actual time=0.771..114.105 rows=710 loops=1)
   Filter: (t ~~* '%abc%'::text)
   Rows Removed by Filter: 99290
 Planning time: 0.056 ms
 Execution time: 114.177 ms
(5 rows)

test=*# create index idx_ronny on ronny (lower(t));
CREATE INDEX
test=*# commit;
COMMIT
test=# analyse ronny;
ANALYZE
test=*# commit;
COMMIT
test=# explain analyse select * from ronny where lower(t) like 'abc*';
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_ronny on ronny  (cost=0.42..8.44 rows=1 width=37) (actual time=0.036..0.036 rows=0 loops=1)
   Index Cond: (lower(t) = 'abc*'::text)
   Filter: (lower(t) ~~ 'abc*'::text)
 Planning time: 0.209 ms
 Execution time: 0.052 ms
(5 rows)

test=*# explain analyse select * from ronny where lower(t) like '*abc*';
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_ronny on ronny  (cost=0.42..8.44 rows=1 width=37) (actual time=0.018..0.018 rows=0 loops=1)
   Index Cond: (lower(t) = '*abc*'::text)
   Filter: (lower(t) ~~ '*abc*'::text)
 Planning time: 0.067 ms
 Execution time: 0.036 ms
(5 rows)

test=*#
Allerdings verwende ich da * und nicht %.

Du kannst Dir die Expension pg_trgm installieren, damit geht dann:

Code: Alles auswählen

test=*# create index trgm_index on ronny using gist (t gist_trgm_ops);
CREATE INDEX
test=*# commit;
COMMIT

test=*# explain analyse select * from ronny where t like '%abcab%';
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on ronny  (cost=4.36..41.20 rows=10 width=37) (actual time=29.017..29.026 rows=2 loops=1)
   Recheck Cond: (t ~~ '%abcab%'::text)
   Rows Removed by Index Recheck: 5
   Heap Blocks: exact=7
   ->  Bitmap Index Scan on trgm_index  (cost=0.00..4.36 rows=10 width=0) (actual time=28.998..28.998 rows=7 loops=1)
         Index Cond: (t ~~ '%abcab%'::text)
 Planning time: 0.154 ms
 Execution time: 29.084 ms
(8 rows)

test=*# explain analyse select * from ronny where t ilike '%abcab%';
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on ronny  (cost=4.36..41.20 rows=10 width=37) (actual time=26.150..26.167 rows=2 loops=1)
   Recheck Cond: (t ~~* '%abcab%'::text)
   Rows Removed by Index Recheck: 5
   Heap Blocks: exact=7
   ->  Bitmap Index Scan on trgm_index  (cost=0.00..4.36 rows=10 width=0) (actual time=26.124..26.124 rows=7 loops=1)
         Index Cond: (t ~~* '%abcab%'::text)
 Planning time: 0.415 ms
 Execution time: 26.211 ms
(8 rows)

test=*# 
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Ciatronical
Benutzer
Beiträge: 53
Registriert: Sa 21. Aug 2010, 13:10

Re: Query Autocomplete mit Sortierung nach Vorkommen in anderer Tabelle

Beitrag von Ciatronical » Sa 20. Jan 2018, 14:00

Hey,

danke für die Tipps.
Die Verzögerung lag in der Sortierung.

Ich habe es mit einem Index in orderitems über parts_id gelöst.

Code: Alles auswählen

create index idx_orderitems on orderitems ( parts_id );
Die Ausführungszeit beträgt jetzt nur noch 4,2ms.

Deshalb habe ich das Modul pg_trgm auch nicht verwendet.

Vielen Dank und ein schönes WE

Ronny

akretschmer
Erfahrener Benutzer
Beiträge: 7404
Registriert: So 29. Okt 2006, 13:35
Wohnort: Kaufbach, Sachsen, Germany, Germany
Kontaktdaten:

Re: Query Autocomplete mit Sortierung nach Vorkommen in anderer Tabelle

Beitrag von akretschmer » Sa 20. Jan 2018, 17:13

wenn ich mir deine Konstruktion des ORDER BYs anschaue, denke ich, das geht einfacher. Du hast vereinfacht:

Code: Alles auswählen

test=# create table parts(id int primary key, name text);
CREATE TABLE
test=*# create table orders(part_id int references parts, qty int);
CREATE TABLE
test=*# insert into parts values (1, 'teil1');
INSERT 0 1
test=*# insert into parts values (2, 'teil2');
INSERT 0 1
test=*# insert into parts values (3, 'teil3');
INSERT 0 1
test=*# insert into orders values (1, 10);
INSERT 0 1
test=*# insert into orders values (1, 5);
INSERT 0 1
test=*# insert into orders values (2, 5);
INSERT 0 1
und suchst:

Code: Alles auswählen

test=*# select p.id, p.name, sum(o.qty) as c from parts p left join orders o on p.id=o.part_id group by p.id, p.name order by c desc nulls last;
 id | name  | c  
----+-------+----
  1 | teil1 | 15
  2 | teil2 |  5
  3 | teil3 |  ¤
(3 rows)

--
-- oder
--

test=*# select p.id, p.name, coalesce(sum(o.qty),0) as c from parts p left join orders o on p.id=o.part_id group by p.id, p.name order by c desc;
 id | name  | c  
----+-------+----
  1 | teil1 | 15
  2 | teil2 |  5
  3 | teil3 |  0
(3 rows)


test=*# 
(das " ¤ " macht mein psql als NULL)

Je nach Datenmenge solltest Du damit noch besser kommen.
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Ciatronical
Benutzer
Beiträge: 53
Registriert: Sa 21. Aug 2010, 13:10

Re: Query Autocomplete mit Sortierung nach Vorkommen in anderer Tabelle

Beitrag von Ciatronical » So 21. Jan 2018, 14:55

Hey,

ich habe deine beiden Varianten getestet. Sie benötigen zwischen 11 und 16ms Ausführungszeit.

Aus meiner Variante habe ich "COALESCE( NULLIF(" durch "NULLS LAST" ersetzt.
Danach habe ich sehr lange gesucht...
Die Ausführungszeit liegt zwischen 4 und 7ms. Warum auch immer..

Eine Kleinigkeit stört mich (optisch) jedoch noch.

Kann man:

Code: Alles auswählen

SELECT ct FROM ( SELECT qty, count( qty ) AS ct FROM orderitems WHERE parts_id = 6880 GROUP BY 1 ORDER BY ct DESC LIMIT 1 ) AS ct ;

nicht irgendwie verbessern so das man keine Unterabfrage mehr benötigt? Ich möchte lediglich ct anzeigen und nicht qty.

Ich meine das in etwa so:

Code: Alles auswählen

SELECT qty::ZEIGE_NICHT, count( qty )  AS ct FROM orderitems WHERE parts_id = 6880 GROUP BY 1 ORDER BY ct DESC LIMIT 1;

Code: Alles auswählen

SELECT ZEIGE_NICHT( qty ),  count( qty )   AS ct FROM orderitems WHERE parts_id = 6880 GROUP BY 1 ORDER BY ct DESC LIMIT 1;
Gibt es so etwas??

Dank und Gruß Ronny

Antworten