indexing inherited tables

[IvanKonevJr]

Hi! We urgently need to index all inherited tables by parent - tell me what to do for this?

[za-arthur]

Hello,

Can you describe your task more detailed? Do you mean inherited tables or partitions? Do you mean creation of indexes for each table or creation of one global index for all childs?

Currently PostgreSQL doesn't support cascade creation of indexes for inherited tables, and you need to do it manually. But it recursively creates indexes for partitions.

[IvanKonevJr]

Creating one global index for all child inherited tables. Need full-text search in all child tables in one query for max performance, besides whether there are no ways to combine several relevant queries.

"But it recursively creates indexes for partitions." Do you mean that it is possible to use one index per query on the parent table, where the result affects all child rows?

[za-arthur]

Unfortunately currently it isn't possible to create a global index. It is an issue not only of RUM, but also of PostgreSQL itself.

"But it recursively creates indexes for partitions." Do you mean that it is possible to use one index per query on the parent table, where the result affects all child rows?

No, it creates separate indexes for each partition.

[IvanKonevJr]

Well, then let me discuss - can you help me to expand the source code of RUM to include all the rows of children in the index - because and since the select on the parent table returns all rows of children tables with unique id.

[za-arthur]

I'm not sure that I understood you correctly. Can you give an example of queries and tables?

[obartunov]

On Tue, Apr 9, 2019 at 12:20 PM Lao Tsing [email protected] wrote:

Well, then let me discuss - can you help me to expand the source code of RUM to include all the rows of children in the index - becouse and since the select on the parent table returns all rows of children tables with unique id.

Did you think about creating view, which effectively is select from all children tables ?

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/postgrespro/rum/issues/55#issuecomment-481172771, or mute the thread https://github.com/notifications/unsubscribe-auth/AGFI4q6ihmsWK5SabSTnRsVWq6MmZGoGks5vfFtdgaJpZM4cjiZh .

-- Postgres Professional: http://www.postgrespro.com The Russian Postgres Company

[IvanKonevJr]

Well, for example, we have electronics reestr and furniture reestr, in each table there are more than a million rows - we are interested in finding an employee who performed some actions on items in both tables through a nice WebQuery DSL.

p.s. materialized view with a huge tables get 2x database size and a lot of cost

[IvanKonevJr]

and what if, during the generation of the rum index with the presence of children's tables, to do it in a loop on each table, then the index will be complete and consistent, since of query on the parent table return all rows of children? this idea is simple, how can you add it to the source code? I would become tester =)

[za-arthur]

I think you can use UNION statement here. And also you can use here a view (not materialized) as Oleg wrote. Does this work for you?

[IvanKonevJr]

Is it possible to build a full-text ranked query for a not materialized view? In the documentation: "CREATE INDEX constructs an index on the specified column(s) of the specified relation, which can be a table or a materialized view."

[IvanKonevJr]

the generation of the rum index with the presence of children's tables, to do it in a loop on each table, then the index will be complete and consistent, since of query on the parent table return all rows of children

is this idea not easy to implement? i think it solves the problem of full-text search on inherited tables and allows in most cases to refuse ElasticSearch

[za-arthur]

the generation of the rum index with the presence of children's tables, to do it in a loop on each table, then the index will be complete and consistent, since of query on the parent table return all rows of children

Unfortunately we haven't such plans. It won't bring much gain, but it requires much more complex changes into RUM. Alternatively you can create inherited indexes manually. Or you may propose a patch in pgsql-hackers.

[IvanKonevJr]

Arthur, can created inherited indexes manually on each children tables to build ranked full-text result?

[za-arthur]

Yes. Let's say we have two tables:

=# \d+ test_rum
 Column |   Type
--------+----------
 t      | text     
 a      | tsvector 
Indexes:
    "rumidx" rum (a)
Child tables: test_rum_2
=# \d+ test_rum_2
 Column |   Type   
--------+----------
 t      | text     
 a      | tsvector 
Indexes:
    "rumidx_2" rum (a)
Inherits: test_rum

The following query uses both indexes:

=# explain (costs off)                                                
SELECT * FROM test_rum WHERE a @@ to_tsquery('pg_catalog.english', 'ever|wrote')
ORDER BY a <=> to_tsquery('pg_catalog.english', 'ever|wrote');
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Sort
   Sort Key: ((test_rum.a <=> '''ever'' | ''wrote'''::tsquery))
   ->  Result
         ->  Append
               ->  Bitmap Heap Scan on test_rum
                     Recheck Cond: (a @@ '''ever'' | ''wrote'''::tsquery)
                     ->  Bitmap Index Scan on rumidx
                           Index Cond: (a @@ '''ever'' | ''wrote'''::tsquery)
               ->  Bitmap Heap Scan on test_rum_2
                     Recheck Cond: (a @@ '''ever'' | ''wrote'''::tsquery)
                     ->  Bitmap Index Scan on rumidx_2
                           Index Cond: (a @@ '''ever'' | ''wrote'''::tsquery)