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)      | 
    "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: create, delete, rename

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?

  • 2
    I might have missed something, but what's wrong with the 73 microsecond execution time? – dezso Apr 12 at 9:48
  • @dezso the table is in a pretty "clean" state right now. Although there are around 6k rows in it, they are organized (i.e. all the changes to an entry are a contiguous set of rows in the table) but that's not going to happen in real use of the application. I am worried about the cost of 1630 because if planets are not aligned at some point, that's how bad it can get, right? – Victor Apr 12 at 9:51
  • 1
    You should really try getting n execution plan of when you have more rows. Try adding a few tens or hundreds of thousand rows and then edit the question. The planets are good for now. – ypercubeᵀᴹ Apr 12 at 9:53
  • 2
    The cost estimate is just a number the planner uses for finding the most efficient query plan. It has no direct effect on the actual performance. – dezso Apr 12 at 9:53
  • Is it necessary to keep the special key and renamed_from columns if you can refer the previous state by its id? Also the comparison of integers is faster than of character varying(512) – Kondybas Apr 12 at 10:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.