Jochem van Dieten wrote:
>
> I am betting that it is the LIKE that is killing you here, it is pretty
> much impossible to index one using a normal index. Which is why you need
> a partial index on the predefined search pattern:
> CREATE INDEX test_idx ON orderhistory USING HASH (changes)
> WHERE (changes ~~ '%ST:%,%'::text);
> Whether it works depends on the cardinality of the table and the
> expected resultset. Just remember that you need to create partial
> indexes for each query pattern.
Correction:
CREATE INDEX t1_idx ON orderhistory (get_status(orderhistory.changes))
WHERE (changes ~~ '%ST:%,%'::text);
Some other thing:
- remove the order by from the view, ordering is no longer guaranteed
after the next join anyway
- use count(1) instead of count(*) so toast tables do not have to be fetched
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com
