4 ms·

Interesting post, but I don't understand his way of deleting rows that aren't in data.json. Creating a separate table seems overly complex: why not just store a

by orf 11y ago

Interesting post, but I don't understand his way of deleting rows that aren't in data.json. Creating a separate table seems overly complex: why not just store a list of ID's in memory and delete any that aren't in that set? Creating a list of 100,000,000 integers in Python takes 900mb (4.2gb for a set!), which isn't that much. Stick it in one big 'DELETE WHERE id NOT IN (...)' query. Or better yet just TRUNCATE beforehand.

fritzy 11y ago
You're right there. This was a naive stab at it that I hadn't properly thought through. I'll try to update the post this weekend.
empthought 11y ago
> Stick it in one big 'DELETE WHERE id NOT IN (...)' query. I think that's a terrible idea, because it will scan the entire NOT IN (...) array for each row. Since it's NOT IN, it will even have to scan the whole thing every time. A temporary table is generally the way to go for unbounded lists of things; there's even support for it in PostgreSQL and the SQL standard.
- orf 11y ago
  I always assumed pg would be smart and use something with a constant lookup time. This[1] is a query plan 9.5 made with a table containing 10,000,000 sequential rows, executing "DELETE WHERE id IN (select * from generate_series(1, 1000000));". Maybe your right, it spends 5.8 seconds on the nested loop. I've only used this for smaller volumes where an extra table might be overkill, but I guess with loads more rows a temporary table is better. 1. http://tatiyants.com/pev/#/plans/plan_1454709615696 http://tatiyants.com/pev/#/plans/plan_1454709615696
  - empthought 11y ago
    PG is doing the smart thing; it's optimizing for the common case of short IN/NOT IN literals in SQL queries. Simple arrays have much better constant factors. If the clause is a subselect (like yours) and is also an actual table (unlike yours), it will be smart and rewrite it as a JOIN, which will generally have better complexity if the indexes are correct.
anarazel 11y ago
Using NOT EXISTS() is a lot better than NOT IN(). The latter, as defined by the SQL standard, has confusing behaviour with NULLs. x NOT IN(y1, y2) is defined to expand to x <> y1 AND x <> y2. If any of these are NULL, the whole expression is NULL. Which means no rows match. Fun, ain't it?

by orf 11y ago

fritzy 11y ago

You're right there. This was a naive stab at it that I hadn't properly thought through. I'll try to update the post this weekend.

empthought 11y ago

> Stick it in one big 'DELETE WHERE id NOT IN (...)' query. I think that's a terrible idea, because it will scan the entire NOT IN (...) array for each row. Since it's NOT IN, it will even have to scan the whole thing every time. A temporary table is generally the way to go for unbounded lists of things; there's even support for it in PostgreSQL and the SQL standard.

orf 11y ago
I always assumed pg would be smart and use something with a constant lookup time. This[1] is a query plan 9.5 made with a table containing 10,000,000 sequential rows, executing "DELETE WHERE id IN (select * from generate_series(1, 1000000));". Maybe your right, it spends 5.8 seconds on the nested loop. I've only used this for smaller volumes where an extra table might be overkill, but I guess with loads more rows a temporary table is better. 1. http://tatiyants.com/pev/#/plans/plan_1454709615696 http://tatiyants.com/pev/#/plans/plan_1454709615696
- empthought 11y ago
  PG is doing the smart thing; it's optimizing for the common case of short IN/NOT IN literals in SQL queries. Simple arrays have much better constant factors. If the clause is a subselect (like yours) and is also an actual table (unlike yours), it will be smart and rewrite it as a JOIN, which will generally have better complexity if the indexes are correct.

anarazel 11y ago

Using NOT EXISTS() is a lot better than NOT IN(). The latter, as defined by the SQL standard, has confusing behaviour with NULLs. x NOT IN(y1, y2) is defined to expand to x <> y1 AND x <> y2. If any of these are NULL, the whole expression is NULL. Which means no rows match. Fun, ain't it?