Count number of occurrences for each attribute

Questions / Help
,
1

I have the honor to program a plant filter search.
Means you can search plants meeting certain conditions (attributes).
But I need some advice for counting number of occurring attributes.

Over simplified records:

- id: 1; attributes: [color: [red, green], size: 10];
- id: 2; attributes: [color: [red], light: [moderate];

The attributes are varying!

The filter search functionality should provide a counter for each attribute:

color: red (2), green (1)
size: 10 (1)
light: moderate (1)

Now if you’ll select moderate - green & size will be (0), red (1).

Sadly PostgreSQL is getting to its limit with this requirement.
Counting attributes is too slow.
So I’m in search of an alternative approach. → Advice needed!

More details:

  • It concerns about 2,5 k records
  • Attributes of all records will have a size of “just” about 10 - 15 MB
  • The first most common filters will reduce the record amount just by about 20%
  • Update of records / attributes won’t be very frequent

Ideas:

Should I just cache a list of all records + attributes in for example ETS?
And than do the counting / filtering with some Enum operations?

Or are there any other good approaches I’m currently blind for?

1 Like
2

It sounds like you are trying to do faceted search. There are some tips in this article that might be helpful for you: Faceted navigation and search | Bun

It is focused on how to do it in Postgres. With 2.5K records I believe you should be able to make this fast just using Postgres.

4 Likes
3

Thanks Joey. Ok, it definitely sound like it’s called faceted search, what I’m trying to do.

The article is very promising. I myself would never have had the idea to solve this via full text search and ts_stat. I will definitely try this way. It will get interesting if some of my deep nested attributes are compatible with this concept. I will see…

Of course I’ll be happy if it works with just Postgres. Because the record set isn’t really that large.

4

So this works perfectly fine. Thanks a lot.

1 Like
5

I’m glad it works :). Thanks for letting me know. I have plans to try it in my own project but haven’t had the time yet.