Re: [HACKERS] [GENERAL] when are indexes used?

InfraRED <infrared@a-b.hu> writes:
> I noticed that indexes are not used sometimes when they could speed up
> queries:

> explain select * from auth where uid=30;
>         Index Scan using auth_uid_key on auth  (cost=2.05 rows=1 width=40)

> explain select * from auth where uid<30;
>         Seq Scan on auth  (cost=2.06 rows=11 width=40)

> explain select * from auth order by uid;                        
>         Sort  (cost=2.06 rows=32 width=40)
>           -> Seq Scan on auth  (cost=2.06 rows=32 width=40)

With only 32 rows in the table, I suspect the machine is making the
right choices here.  (If you actually have more than 32 rows then you
need to vacuum to update the stats...)  Index scans are not some sort of
free magic solution; they cost a lot more per row scanned than
sequential scans.  They aren't necessarily cheaper than a sequential
scan plus in-memory sort, either.

The system uses an index scan when it's possible and apparently cheaper
than a sequential scan.  There are some problems with its estimation
of the relative costs, which I'm hoping to fix for 6.6.  However, the
problems seem to be that it's *under* estimating the cost of indexscans,
not overestimating them.

> persistent views: like select into, but the view gets updated every time
> the table(s) it was created from change. (gives no further functionality
> over views, but when used wisely, can speed up things)

Think you can do this already with rules and/or triggers.  It takes some
thought though.  Maybe some documentation with a worked-out example
would be a good idea.

> inmemory tables:  table data should not be saved to disk (maybe except
> for swapping), because contains rapidly changing data, which would
> expire before restarting the backend

You can get pretty close to this already with fsync off: if you're
touching the table constantly then all its pages will remain in buffer
cache.  A typical Unix system won't bother to write out modified
pages oftener than once every 30 sec, which is hardly worth worrying
about.
        regards, tom lane