Optimierung meines DISTINCT SubQuery

Hi,

ich habe ein Statement mit verschachtelten SubQuerys. In MySQL würde ich das ohne Probleme in 1 Statement bauen. In Postgres ging dies nicht, da ein GROUP nur noch die Spalten des GROUPs anbietet.

SELECT * FROM (
  SELECT
    DISTINCT ON (firstname_simplified, lastname_simplified, street_simplified, housenumber, zipcode, city_simplified)
      * 
    FROM
      ( SELECT * FROM datas WHERE ip <> '' ORDER BY created_at DESC) AS a
  ) AS b
  ORDER BY created_at DESC

Das SQL geht hin, holt die Daten und sortiert diese nach Datum. Dann schaut es nach Dubletten mittels Distinct und nimmt behält den ersten (neusten). Danach Sind die Daten wieder unsortiert und ich sortiere erneut.

Jetzt würde ich gerne wissen, ob das ganze nicht doch viel einfacher geht.

  1. Das ist ein (in Worten: 1) Statement
  2. ich sehe nichts von einem GROUP (BY)
  3. welche Version ist am Start?

Wenn ich dich recht verstanden habe, willst Du sowas:

test=*# select * from blafasel ;
 firstname | lastname |  created
-----------+----------+------------
 first1    | last1    | 2009-01-01
 first1    | last1    | 2009-02-01
 first1    | last1    | 2009-08-01
 first2    | last2    | 2009-07-01
(4 rows)

test=*# select firstname, lastname, created, row_number() over (partition by firstname, lastname order by created desc) from blafasel ; firstname | lastname |  created   | row_number
-----------+----------+------------+------------
 first1    | last1    | 2009-08-01 |          1
 first1    | last1    | 2009-02-01 |          2
 first1    | last1    | 2009-01-01 |          3
 first2    | last2    | 2009-07-01 |          1
(4 rows)

test=*# select firstname, lastname, created from (select firstname, lastname, created, row_number() over (partition by firstname, lastname order by created desc) from blafasel) foo where row_number=1 order by created ;
 firstname | lastname |  created
-----------+----------+------------
 first2    | last2    | 2009-07-01
 first1    | last1    | 2009-08-01
(2 rows)

Ich weiß aber nicht, ob das performanter ist. Und das geht erst ab 8.4.

Andreas

Vielleicht auch zu heiß heute, aber reicht nicht auch dies schon:

test=*# select firstname, lastname, max(created) as created from blafasel group by 1,2 order by 3 desc;
 firstname | lastname |  created
-----------+----------+------------
 first1    | last1    | 2009-08-01
 first2    | last2    | 2009-07-01
(2 rows)

Ansonsten, mach mal etwas Code, was create table und copy enthält, was man via copy$paste sich übernehmen kann, und zeige, was Du für ein Wunschresultat willst.


Andreas

@akretschmer

zu 1: ich weis, aber ich dachte es hilft das word subquery im title zu haben für spätere suchen.

zu 2: natürlich nicht, da beim group by wie geschrieben im select bereich nur felder angegeben werden dürfen die auch im group by enthalten sind.

zu 3: 8.3 (debian 5.0)

bei mysql hätte ich sowas

SELECT DISTINCT ON (firstname_simplified, lastname_simplified, street_simplified, housenumber, zipcode, city_simplified)
      gender, firstname, lastname, street, housenumber, zipcode, city, nationality, email, city, ip, kek, lp, created_at 
FROM datas WHERE ip <> '' ORDER BY created_at DESC

gemacht das führt aber zu einem

ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions

oder alternative

SELECT gender, firstname, lastname, street, housenumber, zipcode, city, nationality, email, city, ip, kek, lp, created_at 
FROM datas WHERE ip <> '' GROUP BY firstname_simplified, lastname_simplified, street_simplified, housenumber, zipcode, city_simplified ORDER BY created_at DESC

da kommt aber dann dieser fehler:

ERROR:  column "datas.gender" must appear in the GROUP BY clause or be used in an aggregate function

beides hatte so in mysql geklappt.

Die Aufgabenstellung:

Entferne alle Dubletten, aber nehme bei den Dubletten nur den neusten.

Daher meine Idee:

  1. Ich nehme die Daten und sortiere die nach Datum. (Neuster zuerst)
  2. Dann mache ich ein distinct. dieser dimmt immer nur den ersten gefundenen Datensatz. Somit immer den aktuellsten durch die Sortierung.
  3. Und sortiere die Daten nochmal nach Datum

Mein erstes SQL funktioniert, ist allerdings wenn man sich die 2 mysql Varianten anschaut, die auch gehen, sehr umständlich und daher ja meine Frage ob es nicht ordentlicher geht.

Hallo,

ich habe das Gleiche vor.

Hier mal ein Beispiel:

create temp table test_table
(
id serial,
ts timestamp without time zone,
name varchar not null,
value varchar not null,
PRIMARY KEY (id)
);

insert into test_table (ts, name, value)
VALUES (‘2009-10-05 00:00:00’, ‘A’, ‘01’),
(‘2009-10-06 00:00:00’, ‘A’, ‘02’),
(‘2009-10-07 00:00:00’, ‘A’, ‘03’),
(‘2009-10-05 00:00:00’, ‘B’, ‘01’),
(‘2009-10-06 00:00:00’, ‘B’, ‘02’),
(‘2009-10-07 00:00:00’, ‘B’, ‘03’),
(‘2009-10-06 00:00:00’, ‘C’, ‘01’),
(‘2009-10-07 00:00:00’, ‘C’, ‘02’),
(‘2009-10-08 00:00:00’, ‘C’, ‘03’);

SELECT test_table.* from test_table
join (
SELECT name, max(ts) FROM test_table
group by name
) t ON t.max = test_table.ts AND t.name = test_table.name

Das Ergebnis ist folgendes:

3;“2009-10-07 00:00:00”;“A”;“03”
6;“2009-10-07 00:00:00”;“B”;“03”
9;“2009-10-08 00:00:00”;“C”;“03”

In einer Tabelle mit 150 Mio. Einträge is das sehr langsam.

Gibt es inzwischen in PostgreSQL einen äquivalenten Ansatz wie von meinem Vorredner beschrieben?

Erstelle mal einen Index:

create index idx_test on test_table (name, ts desc);

und probier:

select distinct on (name) id, ts, name, value from test_table order by name, ts desc;

Mittels

set enable_seqscan=false;

bekomme ich für Deine Tabelle und den Daten:

test=# explain analyse select distinct on (name) id, ts, name, value from test_table order by name, ts desc;
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=0.00..12.41 rows=9 width=76) (actual time=0.052..0.063 rows=3 loops=1)
   ->  Index Scan using idx_test on test_table  (cost=0.00..12.39 rows=9 width=76) (actual time=0.052..0.055 rows=9 loops=1)
 Total runtime: 0.085 ms
(3 Zeilen)

Andreas

Hallo Andreas,

das sieht ja wirklich fantastisch aus! Ich habe dennoch eine Frage:

Da ich 8.2 noch im Produktivsystem verwende, steht mir die Indexerstellung mit DESC nicht zur Verfügung. Gibt es eine Möglichkeit die Indexsortierung in 8.2 etwa durch eine expression zu beeinflussen, um ebenfalls solch ein tolles Ergebnis zu erhalten?

Bis denn

David

Völlig ungetestet: du könntest den Index auf den Timestamp als ‘epoch’::timestamp - dein_timestamp definieren.

Aber ob das hilft, weiß ich nicht.


Andreas