Fwd: Which indexes does a query use?

On 2/27/06, Russell Smith <mr-russ(at)pws(dot)com(dot)au> wrote:
> Not necessarily. How many rows are there in the table at the moment.
> If pg uses and index, it first has to get the index page, then get the
> heap page. So if you have a small number of blocks in the heap it's
> actually cheaper to just scan the heap. I would guess the heap is small
> by the fact that the seq scan only find one row, and finds it in 46.32
> units. The row width isn't high either and that means you get good
> block packing. Probably 80-100 row's per block.

It's about 200 records.

Are you saying that the strategy pg uses is dynamic, in that as the
size of the table grows the strategy changes?

(here is the explain analyze output)
Limit (cost=46.33..46.34 rows=1 width=58) (actual time=2.000..2.000
rows=1 loops=1)
-> Sort (cost=46.33..46.34 rows=1 width=58) (actual
time=2.000..2.000 rows=1 loops=1)
Sort Key: activity_ts
-> Seq Scan on activities (cost=0.00..46.32 rows=1
width=58) (actual time=0.000..2.000 rows=207 loops=1)
Filter: ((activity_user_id = 2) AND (activity_type = 1)
AND ((activity_ts)::timestamp with time zone < now()))
Total runtime: 3.000 ms
(6 rows)

Chris
--
Chris Velevitch
Manager - Sydney Flash Platform Developers Group
www.flashdev.org.au