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

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: David Blasby <dblasby(at)refractions(dot)net>
Cc: Gaetano Mendola <mendola(at)bigfoot(dot)com>,pgsql-hackers(at)postgresql(dot)org
Subject: Re: SELECT * FROM <table> LIMIT 1; is really slow
Date: 2004-05-26 22:28:25
Message-ID: 3104.1085610505@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
David Blasby <dblasby(at)refractions(dot)net> writes:
> I just did another vacuum analyse on the table:

Ah, here we go:

> 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.)

> 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.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: David BlasbyDate: 2004-05-26 23:02:42
Subject: Re: SELECT * FROM <table> LIMIT 1; is really slow
Previous:From: Tom LaneDate: 2004-05-26 22:17:55
Subject: Re: SELECT * FROM <table> LIMIT 1; is really slow

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