Re: Optimize query for listing un-read messages

2014-05-01 22:30 GMT+02:00 Andreas Joseph Krogh <andreas(at)visena(dot)com>:

> På torsdag 01. mai 2014 kl. 21:53:32, skrev Pavel Stehule <
> pavel(dot)stehule(at)gmail(dot)com>:
>
>
>
> 2014-05-01 21:39 GMT+02:00 Andreas Joseph Krogh <andreas(at)visena(dot)com>:
>>
>> På torsdag 01. mai 2014 kl. 21:30:39, skrev Pavel Stehule <
>> pavel(dot)stehule(at)gmail(dot)com>:
>>
>> Hello
>> [snip]
>>
>> I had a perfect success on similar use case with descent ordered partial
>> index
>>
>> http://www.postgresql.org/docs/9.3/interactive/sql-createindex.html
>>
>>
>> I'm not getting good performance. Are you able to craft an example using
>> my schema and partial index?
>>
>
> maybe some like
>
> CREATE INDEX ON message_property (person_id, message_id) WHERE pr.is_read
>
> When I am thinking about your schema, it is designed well, but it is not
> index friendly, so for some fast access you should to hold a cache (table)
> of unread messages
>
>
> Ah, that's what I was hoping to not having to do. In my system, messages
> arrive all the time and having to update a cache for all new messages for
> all users seems messy... Seems I could just as well create a
> message_property for all users when a new message arrives, so I can INNER
> JOIN it and get good performance. But that table will quickly grow *very*
> large...
>

What you need is a JOIN index, that is not possible in Postgres.

I afraid so some "ugly" solutions is necessary (when you require extra fast
access). You need a index (small index) and it require some existing set -
you cannot do index on the difference two sets.

I expect so some flag on the relation "message" - like "it should not be
not read" can helps little bit - and can be used in partial index as
conditions. Other possibility is some variant of partitioning - you can
divide a messages and users to distinct sets and then you decrease a number
of possible combinations.

Regards

Pavel

>
> --
> *Andreas Jospeh Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andreas(at)visena(dot)com
> www.visena.com
> <https://www.visena.com>
>
>