On Mon, Nov 17, 2008 at 8:53 AM, Dimi Paun <[EMAIL PROTECTED]> 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. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
