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) -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
