Skip site navigation (1) Skip section navigation (2)

Re: Bad performance on simple query

From: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
To: "Dimi Paun" <dimi(at)lattica(dot)com>
Cc: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Bad performance on simple query
Date: 2008-11-17 16:53:51
Message-ID: dcc563d10811170853y75b1286fy503d82222b49bf15@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Mon, Nov 17, 2008 at 8:53 AM, Dimi Paun <dimi(at)lattica(dot)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.

In response to

Responses

pgsql-performance by date

Next:From: Dimi PaunDate: 2008-11-17 17:07:01
Subject: Re: Bad performance on simple query
Previous:From: Alan HodgsonDate: 2008-11-17 16:28:51
Subject: Re: Bad performance on simple query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group