Open millken opened 11 months ago
hi, thanks for the report!
before moving to indexes, there are a couple of things I've noticed, which might make columnar storage be a less than ideal candidate for you:
ok, now to indexes!
postgres attempts to choose the best plan available for querying, and is usually right, but we can check those assumptions. you can try disabling the columnar custom scan:
SET columnar.enable_custom_scan = 'f';
this will disable the custom scan, and drop back to either a sequential scan or an index scan based on whichever postgres thinks will be faster. if it gives you a sequential scan, you can disable that as well:
SET enable_seqscan = 'f';
that should be enough to drop you to an index scan, and you will likely see that it is no faster than not using the index (partially due to number 1 in the notes above). you can also use pg_hint_plan
and attempt to hint the planner to use an index. you can learn more on our indexes and indexing strategies page.
Hi there! This is somewhere between a known issue and a limitation of columnar systems.
Right now we have made some effort to improve the performance of index-backed scans when indexes are used by the planner but we have not changed the planner or the columnar custom scan to make better use of indexes. As Jerry said, you can hint to the planner to force it to use an index for a given query - this is mostly a workaround for the fact that we have tuned the planner to prefer our parallel scan for most queries. As a result, indexes will be most useful for power users who want to experiment and try tuning their queries with pg_hint_plan. So in that regard, it's doing what we would expect.
Generally columnar is not ideal for "needle in the haystack" queries like the one you ran (at least, I'm assuming that action_hash
is unique or very nearly so, i.e. has very high cardinality). That said, this is the exact kind of query that a index would help with if it was picked up, since at least in this case the index would help the database narrow down the haystack to a stripe/chunk. But no matter what we do, for this specific query, you will always get better performance for this query with a btree index on a heap table.
@JerrySievert Thank you very much for your reply. I have solved the problem of not being able to use indexes by using pg_hint_plan
.
What's wrong?
I used two tables
public.receipts
andreader.receipts
in the test,public.receipts
is a heap table, andreader.receipts
is generated as columnar using pg_ivm. They have the samehash
indexes onaction_hash
.Then use the following query
I have noticed that queries on columnar databases are relatively slow, and the query performance is comparable to when using no indexes.
explain results