9 votes
Accepted

Query performance on static, large PostgreSQL table

Based on this: protein_id has 1,000 unique values snp_id has 7,500,000 unique values the pair (snp_id, protein_id) uniquely determines a row in the table Typical queries: (a) single SNP/protein ...
3 votes

Index for searching in column where the column starts with the provided value

It seems like all possible substrings of your input are delimited with the character /. To illustrate my point: SELECT string_agg(part, '/') OVER (ORDER BY ord) AS prefix FROM string_to_table('...
3 votes

EXPLAIN ANALYZE does not show what takes time with my GIN index scan

The trigram index had to search through lots of data, probably because the trigrams 502, 022, 221 and so on occur frequently. Part of the problem might be a long GIN pending list. See if VACUUM on the ...
  • 40.8k
2 votes

EXPLAIN ANALYZE does not show what takes time with my GIN index scan

If the wildcard is always at the end of the pattern like in your example, then you can use just a btree index scan to handle it efficiently. But you will need to build the index using ...
  • 35.7k
2 votes
Accepted

EXPLAIN ANALYZE does not show what takes time with my GIN index scan

Your question is a duplicate of this one, but perhaps it's worth an explanation specific to Bitmap Index Scan. The numbers in the explain output: actual time=157.625..157.626, as indicated in the ...
  • 23.9k
2 votes
Accepted

Index for searching in column where the column starts with the provided value

Q: SELECT id FROM table WHERE 'example.org/sub/sub/sub/test.html' LIKE (domain || '%') which gives what I want, but it always does a seq scan of the table, even though that I have an index on the ...
1 vote
Accepted

Query with smaller subset to scan taking exponentially longer?

The slow plan uses an index on just (block_number), and filters 11 million rows. That's expensive nonsense, and should not occur while a more fitting index on (chain_id, block_number) exists. Unless ...
1 vote
Accepted

Seq scan on OR condition

To put it all together, based on these assumptions: Referential integrity is enforced with a FK constraint, so you don't need to join to equipment_equipment at all. Both predicates are selective ...
1 vote

Seq scan on OR condition

You can just change equipment.id = 4 into equipment_id = 4, and then you wouldn't need the join at all.
  • 35.7k
1 vote

Return recursive self joins in PostgreSQL with aggregated data

After more research and referencing Database model for a hierarchical content, I have the following query for some of my requirements. It is not recursive though. SELECT c.id AS c_id, c....
  • 65

Only top scored, non community-wiki answers of a minimum length are eligible