Обсуждение: [GENERAL] when are indexes used?

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)

are there any ways to speed up queries like these?
the exact usage alg. of indexes is documented somewhere?
when is this going to be fixed?


finally some enhancement ideas:

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)

pertable fsync behaviour

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

ps: sorry for my bad english

--
InfraRED of aurora-borealis/Veres Tibor
E-Mail: infrared@a-b.hu

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

>> 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.


I did't know about these guesses, and I provided these only for example.. My
real problem is with a ~6000 row database and a select * ... order by query
which takes more than 5 sec. The same query runs for less than 0.1 sec on mssql
:-((

--
InfraRED of aurora-borealis/Veres Tibor
E-Mail: infrared@a-b.hu