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

Pierre C <lists@peufeu.com>

From: "Pierre C" <lists@peufeu.com>

To: "Josh Berkus" <josh@agliodbs.com>, "Tobias Brox" <tobixen@gmail.com>

Cc: pgsql-performance@postgresql.org, "Mladen Gogala" <mladen.gogala@vmsinfo.com>

Date: 2011-02-11T14:51:44Z

Lists: pgsql-hackers, pgsql-performance

>    select * from account_transaction where trans_type_id in ( ...
> long, hard-coded list ...) and account_id=? order by created desc
> limit 25;

You could use an index on (account_id, created, trans_type), in  
replacement of your index on (account_id, created). This will not prevent  
the "Index Scan Backwards", but at least, index rows with trans_type not  
matching the WHERE clause will not generate any heap access...