On Tue, Jul 2, 2024 at 8:53 AM Aleksander Alekseev <aleksan...@timescale.com> wrote: > CREATE TABLE test1(c char, n bigint); > CREATE INDEX test1_idx ON test1 USING btree(c,n);
The type "char" (note the quotes) is different from char(1). It just so happens that v1 has support for skipping attributes that use the default opclass for "char", without support for char(1). If you change your table definition to CREATE TABLE test1(c "char", n bigint), then your example queries can use the optimization. This makes a huge difference. > EXPLAIN [ANALYZE] SELECT COUNT(*) FROM test1 WHERE n > 900_000_000; For example, this first test query goes from needing a full index scan that has 5056 buffer hits to a skip scan that requires only 12 buffer hits. > I noticed that with the patch we choose Index Only Scans for Test 1 > and without the patch - Parallel Seq Scan. However the Parallel Seq > Scan is 2.4 times faster. Before the patch the query takes 53 ms, > after the patch - 127 ms. I'm guessing that it's actually much faster once you change the leading column to the "char" type/default opclass. > I realize this could be just something > specific to my hardware and/or amount of data. The selfuncs.c costing current has a number of problems. One problem is that it doesn't know that some opclasses/types don't support skipping at all. That particular problem should be fixed on the nbtree side; nbtree should support skipping regardless of the opclass that the skipped attribute uses (while still retaining the new opclass support functions for a subset of types where we expect it to make skip scans somewhat faster). -- Peter Geoghegan
