Postgresql – How do i store hash indices on the database

postgresql

I'm storing (references to) images in a database. I want to have reverse image search, so I wish to store a multidimensional hash of the image – for example width to nearest 10px, height to nearest 10px, and perhaps a downsampled version of the image.

Originally I was going to store these hashes on disk, but then I realised I may be able to store in database in a much more organised (and perhaps performant) manner.

Best Answer

There are several considerations:

Consistency: if you store everything in the database, consistency between database entries and the hash are automatically guaranteed by the transaction system. That simplifies architecture and development.
Read/Write Performance: reading bigger data from the database performs much worse than reading files from the file system.
Backup/Maintenance: large databases are more difficult to back up than large file systems.
Search performance: unless you build special GiST index support for operators on these hashes (which requires C programming and dealing with the guts of PostgreSQL), you won't be able to efficiently search for the hashes in the database.

Related Solutions

PostgreSQL Performance – Use Nested Loop with Indices Over Hash Join

This closely related answer on SO should provide answers to your primary question:
Setting enable_seqscan = off in a single SELECT query

You could use in similar fashion, to disable hash joins for the current transaction:

SET LOCAL enable_hashjoin=off;

But that's not my advice. Read the answer over there.
And this one about statistics and cost settings, too.

More importantly, untangle your query first:

SELECT creation_epoch, user_screen_name, chunk
FROM  (
   SELECT id AS owner_user_id
   FROM   users
   WHERE  reputation > 100000
   ORDER  BY reputation 
   LIMIT  500
   ) u
JOIN   posts p USING (owner_user_id)
JOIN   post_tokenized t USING (id)
WHERE  type = 'tag'
AND    user_screen_name IS NOT NULL;

Should be considerably faster and also make it easier for the query planner to choose the best plan (given sane cost settings and table statistics).

Postgresql – Finding optimal indices, different query competing indices

The query planner seems to think that created is more selective than token. Are your table statistics up to date? Check the last_analyze column in:

SELECT  schemaname, relname, last_analyze 
FROM    pg_stat_all_tables 
WHERE   relname = 'logs'

To really force the issue, you can use a temporary table:

begin;
create temporary table tmp_logs (created timestamp);
insert tmp_logs select created from logs where token = 'token_600_chars_long';
select count(*) from tmp_logs where created >= NOW() - INTERVAL '1 day';
commit;

The first query only uses a condition on token, so the optimizer can only use an index that starts with token.

Best Answer

Related Solutions

PostgreSQL Performance – Use Nested Loop with Indices Over Hash Join

Postgresql – Finding optimal indices, different query competing indices

Related Question