0

We are using PostgreSql and have multiple tables which are laid out approximately like this.

  1. A table for some entity in our system
apple

id | created_at | updated_at | user_id
  1. An enum table with allowed values
quality

value (pk) | comment
---------------
okay       | ...
rotten     | ...
pristine   | ...
  1. A status history table which records the status of that entity over time
apple_quality

id | created_at | updated_at | quality (fk) | apple_id (fk) | valid_at
  1. A view on the status history table which gives the current status for each thing
CREATE
OR REPLACE VIEW current_apple_quality AS
SELECT
  DISTINCT ON (apple_quality.apple_id) 
  apple_quality.id,
  apple_quality.created_at,
  apple_quality.updated_at,
  apple_quality.quality,
  apple_quality.apple_id,
  apple_quality.valid_at
FROM
  apple_quality
ORDER BY
  apple_quality.apple_id,
  apple_quality.valid_at DESC;
  1. Indexes on

(in this example)

  • apple_quality.id
  • apple_quality.apple_id
  • apple_quality.apple_id, apple_quality.valid_at desc

We are running in to issues where queries are needing to make a sequential scan of one of these status tables and its quite a bit too slow.

We are trying to ask questions akin to

"for a specific user, how many apples do they have which are rotten"

And our queries end up with a query plan that looks like this.

              ->  Aggregate  (cost=37256.06..37256.08 rows=1 width=32)
                    ->  Nested Loop  (cost=0.71..37256.06 rows=1 width=8)
                          Join Filter: (apple.id = __be_0_current_apple_quality.apple_id)
                          ->  Subquery Scan on __be_0_current_apple_quality  (cost=0.42..37240.95 rows=544 width=16)
                                Filter: (__be_0_current_apple_quality.quality = 'rotten'::text)
                                ->  Unique  (cost=0.42..35880.96 rows=108799 width=113)
                                      ->  Index Scan using apple_quality_apple_id_valid_at_idx on apple_quality  (cost=0.42..34762.56 rows=447358 width=113)

So what I am wondering is

  • Is there a better way to write the view which is more easily optimized?
  • Are there indexes I should be adding?
  • Is there anything else i can do to improve performance without denormalizing data?

This is a pattern we've used many places in our app, so I figure there might be some well known solutions/tradeoffs I don't have the experience to know about.

I can provide more information upon request.

2
  • Hi, and welcome to dba.se! You say enum table when I think you mean lookup table? There's an enum type but you're not using that AFAICS!
    – Vérace
    Apr 1 at 22:25
  • This is a duplicate of this Stackoverflow question, and since you refuse to answer my questions there, there is little that can be added here. Apr 2 at 7:36

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.