[PostgreSQL] Dubbele entries snel verwijderen?

[Rotje333]

Hey mede-tweakers,

Voor een schoolopdracht ben ik bezig om tweets op te halen over het OV in Nederland.
Dit doe ik met behulp van R, met de functie dbWriteTable().
Nu is het het geval dat ik nogal veel dubbele tweets binnenhaal, deze verwijder ik dan weer aan de hand van een tweet id in een SQL script.

De query is als volgt:

SQL:

DELETE FROM tweet a
WHERE a.ctid <> (SELECT min(b.ctid)
                 FROM   tweet b
                 WHERE  a.id = b.id);

Nu doet dit in principe precies wat ik wil bereiken, echter is er één probleem, de snelheid van deze query.
Ik heb nu in 4 dagen tijd zo'n 50.000 tweets opgehaald, en dit worden er uiteraard elke dag weer een stuk meer. Nu duurt de query voor 50.000 tweets al bijna een kwartier.

Nu zat ik te denken aan een query die alleen de laatste 600 records pakt, en aan de hand daarvan kijkt of er dubbele zijn, echter weet ik niet helemaal hoe ik dit zou kunnen doen.

Is het dus mogelijk om de dubbele records te verwijderen uit de laatste 600 records in PostgreSQL?

Bij voorbaat dank

[storeman]

Dit zou volgens mij niet zo lang hoeven duren, zeker geen kwartier. Ik zou vooral eens naar je geheugeninstellingen kijken en eventueel indexes. Ook met de "EXPLAIN" kom je een heel eind qua performance.

Daarnaast kun je natuurlijk overal een timestamp aanhangen waar je op kunt filteren.

Als je echt de laatste 600 wil selecteren, dan kun je natuurlijk ook de waarde uitlezen van een sequence:

SQL:

SELECT currval('public."tblTable1_FieldID_seq"')

Dit geeft je de huidige waarde van een sequence. Daar zou je dus 600 af kunnen halen.

[Rotje333]

storeman schreef op maandag 25 maart 2013 @ 17:46:
Dit zou volgens mij niet zo lang hoeven duren, zeker geen kwartier. Ik zou vooral eens naar je geheugeninstellingen kijken en eventueel indexes. Ook met de "EXPLAIN" kom je een heel eind qua performance.

Daarnaast kun je natuurlijk overal een timestamp aanhangen waar je op kunt filteren.

Als je echt de laatste 600 wil selecteren, dan kun je natuurlijk ook de waarde uitlezen van een sequence:

SQL:

SELECT currval('public."tblTable1_FieldID_seq"')

Dit geeft je de huidige waarde van een sequence. Daar zou je dus 600 af kunnen halen.

Elke tweet heeft al een timestamp, namelijk die van wanneer de tweet geplaatst is.

Wat je zegt over de sequence begrijp ik niet helemaal, hoe zou ik dat, in dit geval kunnen toepassen?

[BtM909]

We vinden het wel prettig als je iets meer zelfinzet toont, door aan te geven waar je op hebt gezocht, wat je hebt gevonden, waarom dat niet het juiste resultaat was

Een aantal die me te binnen schieten:
- krijg je al een (unieke) tweet id mee voor de insert?
- waarom niet met juiste indexen / uniques om te voorkomen dat je dubbele entries opslaat?
- waarom zit hier niet je oplossing tussen: http://stackoverflow.com/...ate-entries-in-postgresql?

[Zoijar]

Ja, dit is O(n^2) zo, dat is traag. Duplicates verwijderen doe je door eerst te sorteren (n log n) en dan er linear doorheen te lopen (n). Je moet zorgen dat je database dat ook doet. Ik denk dat je dan op zoiets uitkomt, even snel uit het hoofd kan een fout in zitten:

(ohja, select distinct doet dat)

[ Voor 14% gewijzigd door Zoijar op 25-03-2013 18:02 ]

[Rotje333]

BtM909 schreef op maandag 25 maart 2013 @ 18:00:
We vinden het wel prettig als je iets meer zelfinzet toont, door aan te geven waar je op hebt gezocht, wat je hebt gevonden, waarom dat niet het juiste resultaat was

Een aantal die me te binnen schieten:
- krijg je al een (unieke) tweet id mee voor de insert?
- waarom niet met juiste indexen / uniques om te voorkomen dat je dubbele entries opslaat?
- waarom zit hier niet je oplossing tussen: http://stackoverflow.com/...ate-entries-in-postgresql?

Ik schrijf naar de database via een R script, al zou ik deze waardes willen controleren zou ik dus eerst nog alle gegevens op moeten halen uit de database, dit leek mij niet de beste oplossing.

De oplossing die in dat topic gegeven wordt werkt niet volledig, er verdwijnen wel een paar dubbele records, maar niet allemaal. Ik heb werkelijk geen idee waarom niet.

[BasieP]

Rotje333 schreef op maandag 25 maart 2013 @ 17:59:
[...]


Elke tweet heeft al een timestamp, namelijk die van wanneer de tweet geplaatst is.

Wat je zegt over de sequence begrijp ik niet helemaal, hoe zou ik dat, in dit geval kunnen toepassen?

vergeet dat ook, het is onzin.

Sequences zijn niet gemaakt om te gebruiken om dit soort acties te doen dus dat moet je ook niet willen.
Er zouden eens records verwijderd kunnen worden bijv.. je sequence weet dat niet.
Om nog maar te zwijgen van het feit dat sequences (in somige DBMA's) buiten transacties vallen.

[TheNephilim]

Volgens mij krijg je via de Twitter API's netjes een tweet ID binnen, ook als die geretweet is, kun je de originele ID nog achterhalen in retweeted_status of iets dergelijks.

Dan is het een kwestie van een UNIQUE op het opgeslagen tweet ID in je eigen database.

[Rotje333]

Ondertussen heb ik een aardige oplossing gevonden, deze query kost me maar ongeveer 1 seconde

SQL:

CREATE TABLE tmp AS SELECT DISTINCT ON (id) tweet,id,datum,type FROM tweet;
DROP TABLE tweet;
ALTER TABLE tmp RENAME TO tweet;

[Reinier]

Oef dat vind ik echt een vieze oplossing, een tabel droppen om dubbelingen te verwijderen. Je moet er echt voor zorgen dat je in de eerste plaats al geen dubbele tweets in je tabel krijgt.

Of heb je dat ook opgelost en was bovenstaande fix een eenmalige?

[H!GHGuY]

Maar euh, waarom pak je het probleem niet bij de bron aan?
Voeg enkel de tweets toe die nog niet bestaan.

Iets als:
http://stackoverflow.com/...-not-exists-best-practice

edit:
en beter nog zou zijn om, als de tweet IDs enkel stijgen (?), gewoon de maximum tweet ID uit je tabel te halen en zelfs de insert niet doen als de ID niet groter is.

[ Voor 29% gewijzigd door H!GHGuY op 26-03-2013 12:32 ]

[Rotje333]

H!GHGuY schreef op dinsdag 26 maart 2013 @ 12:30:
Maar euh, waarom pak je het probleem niet bij de bron aan?
Voeg enkel de tweets toe die nog niet bestaan.

Iets als:
http://stackoverflow.com/...-not-exists-best-practice

edit:
en beter nog zou zijn om, als de tweet IDs enkel stijgen (?), gewoon de maximum tweet ID uit je tabel te halen en zelfs de insert niet doen als de ID niet groter is.

Reinier schreef op dinsdag 26 maart 2013 @ 11:45:
Oef dat vind ik echt een vieze oplossing, een tabel droppen om dubbelingen te verwijderen. Je moet er echt voor zorgen dat je in de eerste plaats al geen dubbele tweets in je tabel krijgt.

Of heb je dat ook opgelost en was bovenstaande fix een eenmalige?

Het is inderdaad een vieze (tijdelijke) oplossing.

Ik denk dat ik inderdaad toch in mijn R script moet gaan kijken of daar geen mogelijkheid is om dubbele tweet id's niet naar de database te schrijven. Alleen ben ik dan wel benieuwd naar de performance van het script.

Ik zal mijn bevindingen delen.

[BtM909]

Je schrijft naar een database en je wilt dat in 1 kolom de velden UNIQUE blijven?

Zou ik zo je probleemstelling goed omschrijven?

[Rotje333]

BtM909 schreef op dinsdag 26 maart 2013 @ 15:30:
Je schrijft naar een database en je wilt dat in 1 kolom de velden UNIQUE blijven?

Zou ik zo je probleemstelling goed omschrijven?

Precies.

Ik heb even gekeken naar mijn R script, nu haal ik de lijst van id's op die al in de database staan, en ik heb een lijst met 300 opgehaalde tweets, met id.

Nu heb ik dus in R een functie nodig, waarmee ik kan kijken of de id's van de 300 opgehaalde tweets voorkomen in een list. Wanneer de id van zo'n tweet dus al voorkomt, moet hij verwijderd worden uit de lijst met 300 nieuwe tweets.

De tweet id's haal ik gewoon op met:

S:

ids = dbGetQuery(con, "SELECT id FROM tweet')

Heeft iemand een idee hoe ik dit voor elkaar krijg?

[Zoijar]

sql unique constraint

Je database kan meer dan je denkt -- zeker postgresql. Sterker nog, je kan hele programma's schrijven rechtstreeks in je database.