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 ...
- 164k
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('...
- 164k
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 ...
- 29.3k
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 ...
- 164k
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 ...
- 164k
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