From: Justin <justin@emproshunts.com>
To: mark <markkicks@gmail.com>
To: pgsql-general@postgresql.org
Subject: Re: select query takes 13 seconds to run with index
Date: Mon, 26 May 2008 19:26:33 -0400
Message-ID: <483B4729.7050402@emproshunts.com> (raw)
In-Reply-To: <82fa9e310805261549m2362fbe8sbf18789938fee833@mail.gmail.com>
References: <82fa9e310805261549m2362fbe8sbf18789938fee833@mail.gmail.com>
mark wrote:
> Hi, is there anyway this can be made faster? id is the primary key,
> and there is an index on uid..
>
> thanks
>
>
> EXPLAIN ANALYZE select * from pokes where uid = 578439028 order by id
> DESC limit 6;
> QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=0.00..9329.02 rows=6 width=135) (actual
> time=13612.247..13612.247 rows=0 loops=1)
> -> Index Scan Backward using pokes_pkey on pokes
> (cost=0.00..5182270.69 rows=3333 width=135) (actual
> time=13612.245..13612.245 rows=0 loops=1)
> Filter: (uid = 578439028)
> Total runtime: 13612.369 ms
> (4 rows)
>
>
First this should be posted on performance list.
how many records are in this table? The uid 578,439,028 assuming this
is auto incremented key that started 1 this means there is 578 million
records in the table.
The estimate is way off, when was the last time Vaccum was on the table?
What verison of Postgresql are you running
Size of the Table
Table layout
Load on the database
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: justin@emproshunts.com, markkicks@gmail.com, pgsql-general@postgresql.org
Subject: Re: select query takes 13 seconds to run with index
In-Reply-To: <483B4729.7050402@emproshunts.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox