From: | Vitalii Tymchyshyn <tivv00(at)gmail(dot)com> |
---|---|
To: | Tobias Brox <tobixen(at)gmail(dot)com> |
Cc: | Josh Berkus <josh(at)agliodbs(dot)com>, pgsql-performance(at)postgresql(dot)org, Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com> |
Subject: | Re: Why we don't want hints Was: Slow count(*) again... |
Date: | 2011-02-11 09:44:05 |
Message-ID: | 4D5504E5.3060901@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-performance |
11.02.11 11:29, Tobias Brox написав(ла):
> 2011/2/11 Віталій Тимчишин<tivv00(at)gmail(dot)com>:
>> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Dimitri Fontaine | 2011-02-11 09:44:32 | Re: ALTER EXTENSION UPGRADE, v3 |
Previous Message | Dimitri Fontaine | 2011-02-11 09:38:32 | Re: ALTER EXTENSION UPGRADE, v3 |
From | Date | Subject | |
---|---|---|---|
Next Message | Tobias Brox | 2011-02-11 11:26:01 | Re: Why we don't want hints Was: Slow count(*) again... |
Previous Message | Tobias Brox | 2011-02-11 09:29:06 | Re: Why we don't want hints Was: Slow count(*) again... |