Re: Which indexes does a query use?

Chris Velevitch wrote:
> In pg v7.4.5, I have this query:-
>
> select *
> from activities
> where activity_user_id = 2 and activity_type = 1 and activity_ts < now()
> order by activity_ts desc
> limit 1;
>
> where activity_user_id is a non-unique index and activity_ts is a
> non-unique index.
>
> When I explain it, I get:-
>
> Limit (cost=46.33..46.34 rows=1 width=58)
> -> Sort (cost=46.33..46.34 rows=1 width=58)
> Sort Key: activity_ts
> -> Seq Scan on activities (cost=0.00..46.32 rows=1 width=58)
> Filter: ((activity_user_id = 2) AND (activity_type = 1)
> AND ((activity_ts)::timestamp with time zone < now()))
>
> If I'm reading this right, it's telling me that is NOT using any indexes.
>
> Clearly, this is wrong. I would have expected that index on
> activity_user_id would have been used to help find all the records
> efficiently.

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.

If you post "explain analyze" instead of "explain" and possibly the
number row in that table, we might be able to help further, but that is
my best guess from the information given.

Regards

Russell Smith
>
>
> Chris
> --
> Chris Velevitch
> Manager - Sydney Flash Platform Developers Group
> www.flashdev.org.au
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>