11.02.11 11:29, Tobias Brox написав(ла):
2011/2/11 Віталій Тимчишин<tivv00@xxxxxxxxx>:
If the list is hard-coded, you can create partial index on
account_transaction(account_id, created desc) where trans_type_id in ( ...
long, hard-coded list ...)
My idea as well, though it looks ugly and it would be a maintenance
head-ache (upgrading the index as new transaction types are added
would mean "costly" write locks on the table,
Create new one concurrently.
and we can't rely on
manual processes to get it right ... we might need to set up scripts
to either upgrade the index or alert us if the index needs upgrading).
Yep. Another option could be to add query rewrite as
select * from (
select * from account_transaction where trans_type_id =type1 and
account_id=? order by created desc limit 25 union all
select * from account_transaction where trans_type_id =type2 and
account_id=? order by created desc limit 25 union all
...
union all
select * from account_transaction where trans_type_id =typeN and
account_id=? order by created desc limit 25
) a
order by created desc limit 25
This will allow to use three-column index in the way it can be used for
such query. Yet if N is large query will look ugly. And I am not sure if
optimizer is smart enough for not to fetch 25*N rows.
Best regards, Vitalii Tymchyshyn
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
