Re: Why we don't want hints Was: Slow count(*) again...

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux