I have a table called
entry_changes defined as follows:
Table "public.entry_changes" Column | Type | Modifiers --------------+-----------------------------+------------------------------------------------------------ id | integer | not null default nextval('entry_changes_id_seq'::regclass) key | character varying(512) | not null type | entry_change_type | not null ts | timestamp without time zone | not null default now() author_name | character varying | not null renamed_from | character varying(512) | Indexes: "entry_changes_pkey" PRIMARY KEY, btree (id) "entry_change_key_idx" btree (key text_pattern_ops) Check constraints: "entry_changes_check" CHECK (type <> 'rename'::entry_change_type AND renamed_from IS NULL OR type = 'rename'::entry_change_type AND renamed_from IS NOT NULL)
Some explanation first:
id belongs to the change
key belongs to the entry that is being changed
entry_change_type is an
enum defined with the following possible values:
I need to be able to enforce the following constraint: only the owner of the entry (i.e. the person who performed the first change on a particular entry) may perform a rename or a delete.
I am currently having the following query:
with recursive name_history as ( select id, key, type, renamed_from, author_name from entry_changes where key = 'some-key' union all select ec.id, ec.key, ec.type, ec.renamed_from, ec.author_name from entry_changes ec inner join name_history nh on nh.renamed_from = ec.key ) select * from name_history;
And from this I can obviously
order by id limit 1 and this will get me the first user who actually performed an action related to this file.
The query above would not be a problem for me if it were only needed for one entry at a time, but instead I need it on a list of entries and the cost presented by
explain analyze is simply too high.
Here is the query plan:
CTE Scan on name_history (cost=1626.72..1630.76 rows=202 width=1072) (actual time=0.053..0.073 rows=4 loops=1) Output: name_history.id, name_history.key, name_history.type, name_history.renamed_from, name_history.author_name Buffers: shared hit=12 CTE name_history -> Recursive Union (cost=4.42..1626.72 rows=202 width=137) (actual time=0.049..0.068 rows=4 loops=1) Buffers: shared hit=12 -> Bitmap Heap Scan on public.entry_changes (cost=4.42..11.68 rows=2 width=137) (actual time=0.048..0.048 rows=2 loops=1) Output: entry_changes.id, entry_changes.key, entry_changes.type, entry_changes.renamed_from, entry_changes.author_name Recheck Cond: ((entry_changes.key)::text = '/by-victor'::text) Heap Blocks: exact=1 Buffers: shared hit=4 -> Bitmap Index Scan on entry_change_key_idx (cost=0.00..4.42 rows=2 width=0) (actual time=0.036..0.036 rows=2 loops=1) Index Cond: ((entry_changes.key)::text = '/by-victor'::text) Buffers: shared hit=3 -> Nested Loop (cost=0.41..161.10 rows=20 width=137) (actual time=0.004..0.005 rows=1 loops=3) Output: ec.id, ec.key, ec.type, ec.renamed_from, ec.author_name Buffers: shared hit=8 -> WorkTable Scan on name_history nh (cost=0.00..0.40 rows=20 width=516) (actual time=0.000..0.000 rows=1 loops=3) Output: nh.id, nh.key, nh.type, nh.renamed_from, nh.author_name -> Index Scan using entry_change_key_idx on public.entry_changes ec (cost=0.41..8.03 rows=1 width=137) (actual time=0.002..0.002 rows=0 loops=4) Output: ec.id, ec.key, ec.type, ec.ts, ec.author_name, ec.renamed_from Index Cond: ((ec.key)::text = (nh.renamed_from)::text) Buffers: shared hit=8 Planning time: 0.336 ms Execution time: 0.156 ms
So my question is: is there any cleaner approach to this problem when it comes to handling both single and multiple
keys of interest?