hydradatabase / hydradatabase/columnar

[Perf]: Planning time increases linearly with number of stripes

Open
#188 4 comments 0 reactions 1 assignee Claimed by @mkaruza View on GitHub
enhancement
Dominant language
C
Stars
3k
Forks
106
PR merge metrics
No merged PRs in 30d

Description

### What's wrong?

I noticed this because I have two tables with the same data, but one was built with the default 150k rows per stripe and the other with 10k rows per stripe. The table has about 1 billion rows. Both tables have a BTree index on `vid`.

This is an `explain analyze` for the table with 150k rows per stripe:
```
explain analyze select s.amount_in from swap_columnar_vid s where vid in (899171129, 999171129);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_swap_columnar_vid on swap_columnar_vid s (cost=0.57..1945.61 rows=2 width=10) (actual time=21.079..21.093 rows=1 loops=1)
Index Cond: (vid = ANY ('{899171129,999171129}'::bigint[]))
Planning Time: 11.635 ms
Execution Time: 21.899 ms
(4 rows)
```

And now for the table with 10k rows per stripe:
```
d30750a14969=> explain analyze select s.amount_in from swap_columnar_vid_stripe_10k s where vid in (899171129, 999171129);
QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------
------------
Index Scan using idx_vid_on_swap_columnar_vid_stripe_10k on swap_columnar_vid_stripe_10k s (cost=0.57..142.02 rows=2 width=10) (actual time=6.422..6.435 rows
=1 loops=1)
Index Cond: (vid = ANY ('{899171129,999171129}'::bigint[]))
Planning Time: 142.906 ms
Execution Time: 6.474 ms
(4 rows)
```

Notice how the query planning increased proportionally to the increase in number of stripes, and to an unreasonable 140ms. And the plan isn't even a columnar scan. This can be worked around to some extent by using prepared statements to take advantage of plan caching, but still this is a performance issue.

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.