Linked Questions

46
votes
3answers
31k views

What is the optimal data type for an MD5 field?

We are designing a system that is known to be read-heavy (on the order of tens of thousands of reads per minute). There is a table names that serves as a sort of central registry. Each row has a ...
1
vote
2answers
167 views

Show values from list that are NOT returned by query

I have been given a list of values that may or may not be found in a table's column. I just need the subset of the list that is NOT found in a SELECT query. Pretending that the list I was given is: '...
2
votes
2answers
2k views

Real time much greater than `EXPLAIN ANALYZE`'s execution time (index scan)

I want to fetch up to 100 rows based on their id. The id is the primary key of the table. The query that I had written looks like this: select * from table where id = any ($1); where $1 is ...
1
vote
1answer
2k views

Improve Performance on GROUP BY - large table PostgreSQL

I have a Postgres table containing the following three columns: origin (INT, b tree index) destination (INT, b tree index) minutes (SMALLINT, b tree index) The table has ~ 1.4 billion rows. Each row ...
0
votes
2answers
128 views

Horribly performing query, how to fix

select user_id, count(id) as unread_count from notifications where is_read = false and user_id in(select(unnest('{200 user IDs}' :: bigint[]))) group by user_id; Problem is, ...
5
votes
3answers
588 views

Postgres sometimes uses inferior index for WHERE a IN (...) ORDER BY b LIMIT N

We have a PostgreSQL table with ~5 billion rows that has developed a nasty habit of missing the proper indices and doing a Primary Key scan on certain LIMIT operations. The problem generally ...
1
vote
1answer
4k views

Postgres bypassing index with IN clause with 100+ values?

I came across this on another forum: PG has a limit of 100 for values in IN queries after which the index on said column is not used. For ex: a typical SELECT ... WHERE IN (...) query on the PK ...
4
votes
1answer
3k views

PostgreSQL - Datetime ranges overlap

I have a table with datetime fields start and end. And I have a list of (start, end) items. I need to check which items from the list overlap with data in the table. The current query looks like this: ...
3
votes
1answer
67 views

Return values at more/less specific time points

In PostgreSQL 10.0, I have a big table with two columns: CREATE TABLE xs ( ts timestamp with time zone NOT NULL, x integer NOT NULL ) Now, if I have 100 given time points, how do I return ...
1
vote
2answers
860 views

Very slow WHERE IN query on very large table - postgresql 9.6

My table (with 140 000 000 rows) : CREATE TABLE attendees( attendee_id VARCHAR NOT NULL, name VARCHAR NOT NULL, status VARCHAR NOT ...
5
votes
2answers
919 views

Optimize a query with small LIMIT, predicate on one column and order by another

I'm using Postgres 9.3.4 and I have 4 queries that have very similar inputs but have vastly different response times: Query #1 EXPLAIN ANALYZE SELECT posts.* FROM posts WHERE posts.source_id IN (...
5
votes
1answer
87 views

How to properly implement compound greatest-n filtering

Yep, more greatest-n-per-group questions. Given the a table releases with the following columns: id | primary key | volume | double precision | chapter |...