On Mon, May 26, 2008 at 4:49 PM, Justin <justin@xxxxxxxxxxxxxxx> wrote: > mark wrote: > > On Mon, May 26, 2008 at 4:26 PM, Justin <justin@xxxxxxxxxxxxxxx> wrote: > 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. > > > sorry about this. > > > > how many records are in this table? > > > 22334262, 22 million records. > > > > The estimate is way off, when was the last time Vaccum was on the table? > > > about a week ago i ran this VACUUM VERBOSE ANALYZE; > this table is never updated or deleted, rows are just inserted... > > > > > What verison of Postgresql are you running > > > 8.3.1 > > > > Size of the Table > > > 22 million rows approximately > > > I have no experience on large datasets so people with more experience in > this area are going to have to chime in. > My gut feel is 13 seconds for Postgresql to sort through an index of that > size and table is not bad. > > you may need to take a look at hardware and postgresql.config settings to > improve the performance for this query > > This query is very simple where changing it around or adding index results > massive improvements is not going to help in this case. the hardware is e5405 dual quad core on a 16GB RAM machine, with 8.3.1 default settings except maximum connections increased...
