[Q&A] Can you make partitioned indexes on inheritance partitions?

[kafu24]

What happens?

I've made partitioned indexes on declarative partitions but I can't get it to work for inheritance partitions. I'm guessing it isn't supported right now but wanted to check if I was just doing something wrong instead.

To Reproduce

This is me trying to make it work with the test table.

CALL paradedb.create_bm25_test_table(
  schema_name => 'public',
  table_name => 'mock_items2'
);
CREATE TABLE mock_items (LIKE mock_items2 INCLUDING CONSTRAINTS INCLUDING INDEXES);
CREATE TABLE mock_items_y2023m04 (
    CHECK (created_at >= TIMESTAMP '2023-04-01' AND created_at < TIMESTAMP '2023-05-01')
) INHERITS (mock_items);
INSERT INTO mock_items_y2023m04 SELECT * FROM mock_items2 WHERE created_at >= '2023-04-01' AND created_at < '2023-05-01';
CREATE TABLE mock_items_y2023m05 (
    CHECK (created_at >= TIMESTAMP '2023-05-01' AND created_at < TIMESTAMP '2023-06-01')
) INHERITS (mock_items);
INSERT INTO mock_items_y2023m05 SELECT * FROM mock_items2 WHERE created_at >= '2023-05-01' AND created_at < '2023-06-01';
DROP TABLE mock_items2;
CREATE INDEX mock_items_search_idx ON mock_items
USING bm25 (id, description, category, rating, in_stock, created_at, metadata, weight_range)
WITH (key_field='id');
ALTER TABLE mock_items_y2023m04 ADD PRIMARY KEY (id);
CREATE INDEX mock_items_y2023m04_search_idx ON mock_items_y2023m04
USING bm25 (id, description, category, rating, in_stock, created_at, metadata, weight_range)
WITH (key_field='id');
ALTER TABLE mock_items_y2023m05 ADD PRIMARY KEY (id);
CREATE INDEX mock_items_y2023m05_search_idx ON mock_items_y2023m05
USING bm25 (id, description, category, rating, in_stock, created_at, metadata, weight_range)
WITH (key_field='id');
CREATE OR REPLACE FUNCTION mock_items_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.created_at >= TIMESTAMP '2023-04-01' AND
         NEW.created_at < TIMESTAMP '2023-05-01' ) THEN
        INSERT INTO mock_items_y2023m04 VALUES (NEW.*);
    ELSIF ( NEW.created_at >= TIMESTAMP '2023-05-01' AND
            NEW.created_at < TIMESTAMP '2023-06-01' ) THEN
        INSERT INTO mock_items_y2023m05 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Timestamp out of range. Fix the mock_items_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER insert_mock_items_trigger
    BEFORE INSERT ON mock_items
    FOR EACH ROW EXECUTE FUNCTION mock_items_insert_trigger();
EXPLAIN ANALYZE SELECT * FROM mock_items WHERE description @@@ 'metal';

Query planner shows it's not looking at the child indexes:

                                                                      QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
 Custom Scan (ParadeDB Scan) on mock_items  (cost=10.00..10.01 rows=1 width=641) (actual time=0.080..0.081 rows=0 loops=1)
   Table: mock_items
   Index: mock_items_search_idx
   Segment Count: 0
   Heap Fetches: 0
   Exec Method: NormalScanExecState
   Scores: false
   Tantivy Query: {"with_index":{"query":{"parse_with_field":{"field":"description","query_string":"metal","lenient":null,"conjunction_mode":null}}}}
 Planning Time: 2.467 ms
 Execution Time: 0.171 ms
(10 rows)

When you do it with declarative partitioning, you can see that it does look at them:

DROP TABLE mock_items CASCADE;
CALL paradedb.create_bm25_test_table(
  schema_name => 'public',
  table_name => 'mock_items2'
);
CREATE TABLE mock_items (LIKE mock_items2 INCLUDING CONSTRAINTS) PARTITION BY RANGE (created_at);
CREATE TABLE mock_items_1 PARTITION OF mock_items FOR VALUES FROM ('2023-04-01') TO ('2023-05-01');
CREATE TABLE mock_items_2 PARTITION OF mock_items FOR VALUES FROM ('2023-05-01') TO ('2023-06-01');
INSERT INTO mock_items SELECT * FROM mock_items2;
DROP TABLE mock_items2;
CREATE INDEX mock_items_search_idx ON mock_items
USING bm25 (id, description, category, rating, in_stock, created_at, metadata, weight_range)
WITH (key_field='id');
EXPLAIN ANALYZE SELECT * FROM mock_items WHERE description @@@ 'metal';

                                                                                     QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=10.00..20.03 rows=2 width=641) (actual time=0.331..0.335 rows=1 loops=1)
   ->  Custom Scan (ParadeDB Scan) on mock_items_1  (cost=10.00..10.01 rows=1 width=641) (actual time=0.113..0.113 rows=0 loops=1)
         Table: mock_items_1
         Index: mock_items_1_id_description_category_rating_in_stock_create_idx
         Segment Count: 1
         Heap Fetches: 0
         Exec Method: NormalScanExecState
         Scores: false
         Tantivy Query: {"with_index":{"query":{"with_index":{"query":{"parse_with_field":{"field":"description","query_string":"metal","lenient":null,"conjunction_mode":null}}}}}}
   ->  Custom Scan (ParadeDB Scan) on mock_items_2  (cost=10.00..10.01 rows=1 width=641) (actual time=0.217..0.219 rows=1 loops=1)
         Table: mock_items_2
         Index: mock_items_2_id_description_category_rating_in_stock_create_idx
         Segment Count: 1
         Heap Fetches: 1
         Exec Method: NormalScanExecState
         Scores: false
         Tantivy Query: {"with_index":{"query":{"with_index":{"query":{"parse_with_field":{"field":"description","query_string":"metal","lenient":null,"conjunction_mode":null}}}}}}
 Planning Time: 1.299 ms
 Execution Time: 0.445 ms
(19 rows)

OS:

Debian 12 x64

ParadeDB Version (e.g. output of SELECT * FROM paradedb.version_info();):

0.18.9

Are you using ParadeDB Docker, Helm, or the extension standalone?

ParadeDB Docker Image

Full Name:

Gordon Luu

Affiliation:

Student

Did you include all relevant data sets for reproducing the issue?

Yes

Did you include the code required to reproduce the issue?

• Yes, I have

Did you include all relevant configurations (e.g., CPU architecture, PostgreSQL version, Linux distribution) to reproduce the issue?

• Yes, I have

[stuhood]

Hey @kafu24! Yea, we do not have explicit support for inheritance partitions.

From what I can tell it would require a bit of fiddling in our custom scan to produce a nested plan, which is not necessary for declarative partitioning, since the parent table cannot hold data.

If you'd like to make a case for supporting them (because they enable a use case that you cannot achieve otherwise maybe), then please feel free to open a ticket. But we're relatively unlikely to pursue them given that the alternative is better supported in most cases.