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

Re: SELECT * FROM <table> LIMIT 1; is really slow

From: David Blasby <dblasby(at)refractions(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: SELECT * FROM <table> LIMIT 1; is really slow
Date: 2004-05-26 23:02:42
Message-ID: 40B52212.4010700@refractions.net (view raw or flat)
Thread:
Lists: pgsql-hackers
Tom Lane wrote:
>>INFO:  "csn_edges": found 0 removable, 16289929 nonremovable row 
>>versions in 2783986 pages
> 
> 
> That works out to just under 6 rows per 8K page, which wouldn't be too
> bad if the rows are 1K wide on average, but are they?  (You might want
> to run contrib/pgstattuple to get some exact information about average
> tuple size.)

The rows are "wide" - there's a PostGIS geometry present.


>>INFO:  analyzing "public.csn_edges"
>>INFO:  "csn_edges": 2783986 pages, 3000 rows sampled, 6724 estimated 
>>total rows
> 
> 
> This looks like a smoking gun to me.  The huge underestimate of number
> of rows from ANALYZE is a known failure mode of the existing sampling
> method when the early pages of the table are thinly populated.  (Manfred
> just fixed that for 7.5, btw.)
> 
> I think you want to VACUUM FULL or CLUSTER the table, and then take a
> look at your FSM settings and routine vacuuming frequency to see if
> you need to adjust them to keep this from happening again.


I'm now clustering - thanks for the help!

The history of this table is quite short - I just created it last week.

The original table had a bigint column that I converted to int (using 
the "alter table csn_edges rename to csn_edges_backup;CREATE TABLE 
csn_edges AS SELECT a,b,c::int,d,e FROM csn_edges; delete table 
csn_edges_backup;" trick).  I dont think there were any changes to the 
current csn_edges table after it was created.


I have another copy of this table in another database - vacuum analyse 
verbose says its "only" 1,500,000 pages (vs 2,800,000).  Shouldnt vacuum 
know your table is wasting 10Gb of space and fix it for you?  Or at 
least HINT?  Or a "TIDY" command?


Should I be upping my FSM to 2,000,000 pages?

dave






In response to

Responses

pgsql-hackers by date

Next:From: pgsqlDate: 2004-05-26 23:14:12
Subject: Re: tablespaces and DB administration
Previous:From: Tom LaneDate: 2004-05-26 22:28:25
Subject: Re: SELECT * FROM <table> LIMIT 1; is really slow

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