On Mon, Nov 17, 2008 at 8:53 AM, Dimi Paun <dimi@lattica.com> wrote:
> Hi folks,
>
> I have a simple table that keeps track of a user's access history.
> It has a a few fields, but the important ones are:
> - ownerId: the user's ID, a int8
> - accessTS: the timestamp of the record
>
> The table right now is small, only 1942 records.
> The user I test with (10015) has only 89 entries.
>
> What I want is to get the last 5 accesses of a user:
> SELECT * FROM triphistory WHERE ownerId = 10015 ORDER BY accessTS DESC LIMIT 5
>
> If I create a composite index *and* analyze:
> create index IDX_TRIP_HISTORY_OWNER_ACCESS_TS on tripHistory (ownerId, accessTS);
> ANALYZE triphistory;
>
> It takes 0.091s (!):
> perpedes_db=# EXPLAIN ANALYZE SELECT * FROM triphistory WHERE ownerId = 10015 ORDER BY accessTS DESC LIMIT 5;
> QUERY PLAN
>
------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=0.00..7.99 rows=5 width=106) (actual time=0.024..0.042 rows=5 loops=1)
> -> Index Scan Backward using idx_trip_history_owner_access_ts on triphistory (cost=0.00..142.20 rows=89
width=106)(actual time=0.021..0.034 rows=5 loops=1)
> Index Cond: (ownerid = 10015)
> Total runtime: 0.091 ms
> (4 rows)
>
>
> BTW, this is after several runs of the query, shouldn't all this stuff be in memory?
Are you saying it's excessive you need the compound query? Cause
that's running in 91microseconds as pointed out by Alan.