1

I'm dealing with slow queries due to the sheer amount of possibilities. I'm looking for a better solution or help with the solution I already have in motion.

Background Context:

We have people in our system with role X whose job is to approve/deny content in a table called stories.

We have people in our system with role Y (referred to as owners) whose job is to look at ONLY approved content from role X and may or may not "claim" the content. However, role Y only sees records on platform_story, not the stories themselves. Also there's a "filter" that whittles out content not relevant to the owners.

We have this concept known as "claiming" content which lives in the claims table. If an owner claims a piece of content, they cannot claim it more than once.

Table Info:

Relationships:

  • stories is one-to-many with platform_story
  • platform_story is one-to-many with claims
  • owners is one-to-many with claims
  • pools is one-to-many with owners
  • pools is one-to-many with stories

The pools is a way to reduce the number of owners and content we're dealing with. Just to give you an idea I wouldn't see more than 10,000 owners in one pool and no more than 10,000 stories in one pool.

Current total records:

  • claims has about 2.1 million records
  • platform_story has about 147,419 records
  • stories has about 125,000 records
  • owners has about 24,784 records
  • pools has about 125 records

Problem:

The root of the problem is the sheer amount of content available to owners combined with the fact that it is CPU intensive (from a non-query, programmer perspective) to determine if a piece of content is relevant to a particular owner.

My first thought was to make a pivot table which cached the relationships between owner's and platform_story records they are eligible for... but I quickly realized that's a problem because the table would take up a ton of space.

For example say we made a table called owner_content which had the following columns:

  • owner_id
  • platform_story_id

If it's omitted from the table, that would basically mean that platform_story isn't relevant to that owner.

Let's say you have 5,000 platform_story records that the software deems relevant for 5,000 owners. Well that's 5,000 * 5,000 = 25 million rows. And that's the typical size of ONE pool.

What I've done so far:

I tried to come up with a way to use a VIEW so that the data didn't need to exist and maybe Postgres could whittle down the amount based on other indexes hopefully supplied when querying the view.

I added a column owners.eligible_index and platform_story.eligible_owners.

Then I made a view like so:

CREATE VIEW prospects_view AS
SELECT
    o.pool_id AS pool_id,
    o.id AS owner_id,
    s.id AS story_id,
    ps.id AS platform_story_id,
    ps.platform_id AS platform_id
FROM owners o
INNER JOIN stories s ON (
    s.pool_id = o.pool_id
    AND s.deleted_at IS NULL
)
INNER JOIN platform_story ps ON (
    ps.story_id = s.id
    AND substr(ps.eligible_owners, o.eligible_index + 1, 1) = '1'
)
WHERE o.deleted_at IS NULL

You'll notice ps.eligible_owners is a TEXT type, mostly because the integer columns are not big enough and I didn't know Postgres had binary types. This is a LOT faster than before to find these records, but I was hoping it could be faster if I stored eligible_owners as a binary type.

I dug into Postgres docs and found that there is a couple different types: bit(n), bit varying, and bytea. I not sure why you would use one over the other, if there's any performance advantages being a limited size for example. I feel like there should be a quick way to do a bitwise & to find eligible content for an owner using one of these.

Knowing the structure of my view, what type is the best for my view and why?

1 Answer 1

0

You should use bit(n) or bit varying, depending on whether they are fixed length or varying length. Because that is what they exist for, so using anything else would be an unnecessary digression. And they should be about 8 times smaller than your existing implementation.

But I would note that if nearly every story is relevant to nearly every owner, then the whole concept of "relevance" seems rather pointless.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.