Re: Efficient sorting the results of a join, without denormalization

On Sun, May 31, 2015, at 01:16 PM, Francisco Olarte wrote:
>
> It may seem, and be, unideal from a redundancy perspective, but keys
> are more natural. It means you have user (Glen), folder (Glen, PGlist)
> and message (Glen,PGlist,27), different from (Glen,Inbox,27) or (Glen,
> PgList,28) or (Francisco,PgList,27) ( Where the 'tuples' I've printed
> are the PK values ). This has a lot of advantages, which you pay for
> in other ways, like redundancies, but having composite primary keys
> sometimes work in your favor as you can express restrictions with the
> relationships and build composite indexes for add hoc queries. In this
> case ( an email database ), a serial could be used ( instead of the
> name ) for the user and folder PK, but still have very fast, simple
> queries from a MUA for things like 'select * from messages where
> user_id = <Prefetched_id> and not read order by timestamp desc limit
> 100'. Also it will help catch things like mismatching folder ids, or
> using the user id as folder id, which are easily made when all the
> keys are synthetic and meaningless numbers.
>
>
> As an example, I have a currency table, with it's serial key
> currency_id, and a seller table, which sells just a currency and whose
> pk is (currency_id+seller_id), and a rate table with rates
> (currency_id, rate_id), and an allowed rates table ( to see which
> rates a seller can use ), with primay key (currency_id, seller_id,
> rate_id) and foreign keys (currency_id, seller_id) and (currency_id,
> rate_id) ( it is more or less a classical example. The composite keys
> guarantee I can only allow a seller to sell rates on her currency.
>
> I can also, if needed, build unique indexes on any single id ( they
> are all serials, as I have no other candidate keys ), if I need them,
> but given the access patterns I normally have all of them, and things
> like populating a drop box to allow new rates for a seller are very
> easy.
>
> Francisco Olarte.

Thanks Francisco, that makes sense. I've started moving my code to that,
and it eliminates all the performance issues I had.

I guess I was really hoping there would exist some sort of "dereference"
option when indexing, so I could dereference a foreign key, and then
index on a attribute of that row. E.g. So I could have created an index
such as:

deref(deref(mail.folder_id).user_id, created_at)