PostgreSQL – How to Join External Data in SELECT Query

optimizationperformancepostgresqlpostgresql-performancetemporary-tables

I have few (~20) names of products corresponding to an integer id that needs to be joined on products while exporting products data (using COPY (<query>) TO). The burden is that these names do not rest in DB, rather in Redis (rails's translations fyi). Therefore, I'll be collecting these names before each export. Now, having these names outside the DB, how can I build the query to join them?

I've tried to join these names on-the-fly:

SELECT
...
LEFT JOIN (
    SELECT unnest('{1,2,3}'::int[]) AS id,
           unnest($${"name1", "name2", "name3"}$$::text[]) AS name
) AS product_names ON product_names.id = products.type_id
...

But I've got busted by the amount of products rows (~500k) and lacking indices for that structure. Is there some (better) way to join these data on-fly? I guess one cannot index these on-fly data though.

Which is a good way to solve that situation? I can think of creating temporary table like:

SELECT ... INTO TEMPORARY ...;

but is it wise to have that stay on for long transaction, while exporting (~30 mins)? Or can I omit transactions while using temporary tables and how? Or am I just troubling myself with needless problems while the best solution is to just store those names as a regular table, only truncating it before each export to have actual names?

— postgres 9.3.10

Best Answer

Assuming current Postgres 9.4.

Since you are dealing with just ~ 20 names of products, it's largely irrelevant how you provide those to the query. An index would be a waste of time, Postgres is going to use a sequential scan anyway (judging from what you disclosed in the question).

You can use a temporary table, which lives for the duration of the session, not just transaction like you seem to assume. And you can even create indexes on temp tables, it just wouldn't help in this case.
Be sure to run ANALYZE on temp tables manually:

Are regular VACUUM ANALYZE still recommended under 9.1?

Or you can unnest a provided array literal just as well. But don't rely on the outdated trick of unnesting two arrays in the SELECT list in parallel. This is error prone. Move it to the from clause. I'd suggest:

SELECT
...
LEFT JOIN unnest('{name1,name2,name3}'::text[]) WITH ORDINALITY
  product_names (name, type_id) USING (type_id)
...

Order of returned record with IN statement postgres

Assuming the column name type_id is unambiguous in the tables to the left.

If you don't actually have ascending type_id starting with 1 like your example suggests, consider the new variant of unnest() taking multiple array parameters:

SELECT
...
LEFT JOIN unnest('{4,7,9}'::int[], '{name1,name2,name3}'::text[])
  product_names (type_id, name) USING (type_id)
...

Passing arrays to stored procedures in Postgres

Query

Just your query, formatted:

SELECT u.id_user, u.username
FROM   "user" u 
LEFT   JOIN friend_request fr ON fr.sent_to = u.id_user
                             AND fr.sent_from = 288
LEFT   JOIN friends f ON f.friend_to = u.id_user
                     AND f.friend_of = 288
WHERE  u.username LIKE '%tom%'
AND    fr.sent_to IS NULL
AND    f.friend_to IS NULL;

Or, a variant with NOT EXISTS anti-semi-joins:

SELECT u.id_user, u.username
FROM   "user" u
WHERE  NOT EXISTS (
   SELECT 1 FROM friend_request
   WHERE  sent_to = u.id_user
   AND    sent_from = 288
   ) 
AND    NOT EXISTS (
   SELECT 1 FROM friends
   WHERE  friend_to = u.id_user
   AND    friend_of = 288
   )
WHERE  u.username LIKE '%tom%';

Indexes

The most important factor for performance. An ideal setup would have:

A trigram GIN or GiST index on "user".username. You need the additional module pg_trgm. Instructions - and Gin or GiST?
- How is LIKE implemented?
If index-only scans are possible, append the otherwise useless id_user. You need the additional module btree_gin (or btree_gist respectively):
- Inner join using an array column
Then:
```
CREATE INDEX usr_trgm_idx ON "user" USING gin (username gin_trgm_ops, id_user);
```
Multicolumn indexes (or a UNIQUE / PK constraint) on (sent_from, sent_to) and (friend_of, friend_to) - with columns in that order. The reversed order would work, too, but not quite as good. Each query checks for a single friend_of combined with many different friend_to, which is faster with the suggested column order, because this way tuples are much rather localized on the same or a few index pages.

That said, the proof of the pudding is still in the eating. Get the final verdict from EXPLAIN (BUFFERS, ANALYZE) in your production database.

Aside

Do not call your table user, that's a reserved word.

PostgreSQL Performance – ‘Could Not Write to Hash-Join Temporary File: No Space Left on Device’

1- How do the internals of postgres work that cause this message to occur in the first place?

When joining two very large data sets, one strategy PostgreSQL can use is to scan one table and hash its join columns. Then scan the other table and compare the hashes of the join columns to hashes calculated for the second table.

See https://en.wikipedia.org/wiki/Hash_join

It works really well when you're joining a small table against a much larger one, especially where no indexes suit the join conditions.

PostgreSQL can use a hash join strategy for bigger tables where it writes the hash table partitions out to disk first. This is presumably what you got.

What is the hash-join in reference to the creation of the temporary file that the db creates?

Hash tables. I don't really understand this question.

2- How can I predict and prevent errors like this (related to performance and memory) occurring in the future?

Have more spare disk space for temporary storage. It doesn't have to be in the main tablespace; see the temp_tablespaces setting.

As far as I know PostgreSQL doesn't have the ability to estimate hash table sizes and decide the hash table will be too big for the available disk space.

Best Answer

Related Solutions

PostgreSQL – Best Query to Exclude Existing Friends

Query

Indexes

Aside

PostgreSQL Performance – ‘Could Not Write to Hash-Join Temporary File: No Space Left on Device’

Related Question