4 ms·

Why not simply full indexes?

kornish 9y ago
As I understand it, the types of analysis they want to do actually aren't super amenable to indexing because they may include wildcards in the middle of the search string (e.g. `WHERE text ILIKE 'div.button % .login'`. You can make a btree index on the `text` column, but queries with the above `WHERE` clause won't use the index. To index those effectively, you'd need to create a trigram index in Postgres, which takes up a TON of space. Because 90%+ of rows won't satisfy any of the predicates defined by the customer, it's probably unnecessary to trigram index every row. Out of interest, malisper, how much space do you think you save by using partial indices? edit: removed a statement I couldn't support about taking unnecessary CPU
- malisper 9y ago
  > To index those effectively, you'd need to create a trigram index in Postgres, which takes up a TON of space. I actually ran some experiments on using a naive trigram index and found it doesn't currently don't work for our use case. Most of our data is highly redundant (e.g. Every CSS hierarchy contains `div`) in it. This causes every query to read a large portion of the trigram index which makes the query really slow. I've heard the developers of pg_trgm improved the performance of this use case in a recent version of pg_trgm. I haven't reran my experiment yet because Heap is currently on Postgres 9.5, and the new version of pg_trgm is only available for 9.6+. I believe partial indexes collectively account for ~5% of our disk storage. A trigram index alone would cause an about 30% increase in our disk usage. To eliminate partial indexes, we would also need full indexes on other commonly queried fields such as the url or the text of what was clicked on. Each of these indexes adds a solid amount of space.
malisper 9y ago
My manager wrote a blog post explaining partial indexes and some of the advantages of them: https://blog.heapanalytics.com/speeding-up-postgresql-queries-with-partial-indexes/ https://blog.heapanalytics.com/speeding-up-postgresql-querie...
- jonatron 9y ago
  In that blog post, you store "time" as BIGINT. Does it work better than TIMESTAMP?
  - ddorian43 9y ago
    It's same. Timestamp also uses bigint. It's nicer to use timestamp I guess since you get many datetime functions.

As I understand it, the types of analysis they want to do actually aren't super amenable to indexing because they may include wildcards in the middle of the search string (e.g. `WHERE text ILIKE 'div.button % .login'`. You can make a btree index on the `text` column, but queries with the above `WHERE` clause won't use the index. To index those effectively, you'd need to create a trigram index in Postgres, which takes up a TON of space. Because 90%+ of rows won't satisfy any of the predicates defined by the customer, it's probably unnecessary to trigram index every row. Out of interest, malisper, how much space do you think you save by using partial indices? edit: removed a statement I couldn't support about taking unnecessary CPU

malisper 9y ago
> To index those effectively, you'd need to create a trigram index in Postgres, which takes up a TON of space. I actually ran some experiments on using a naive trigram index and found it doesn't currently don't work for our use case. Most of our data is highly redundant (e.g. Every CSS hierarchy contains `div`) in it. This causes every query to read a large portion of the trigram index which makes the query really slow. I've heard the developers of pg_trgm improved the performance of this use case in a recent version of pg_trgm. I haven't reran my experiment yet because Heap is currently on Postgres 9.5, and the new version of pg_trgm is only available for 9.6+. I believe partial indexes collectively account for ~5% of our disk storage. A trigram index alone would cause an about 30% increase in our disk usage. To eliminate partial indexes, we would also need full indexes on other commonly queried fields such as the url or the text of what was clicked on. Each of these indexes adds a solid amount of space.

malisper 9y ago

My manager wrote a blog post explaining partial indexes and some of the advantages of them: https://blog.heapanalytics.com/speeding-up-postgresql-queries-with-partial-indexes/ https://blog.heapanalytics.com/speeding-up-postgresql-querie...

jonatron 9y ago
In that blog post, you store "time" as BIGINT. Does it work better than TIMESTAMP?
- ddorian43 9y ago
  It's same. Timestamp also uses bigint. It's nicer to use timestamp I guess since you get many datetime functions.