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

From: David Blasby <dblasby(at)refractions(dot)net>
To: David Blasby <dblasby(at)refractions(dot)net>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: SELECT * FROM <table> LIMIT 1; is really slow
Date: 2004-05-27 16:26:54
Message-ID: 40B616CE.7090907@refractions.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I did a CLUSTER - it took just under 12 hours. The original load of the
table (including parsing all the geometries) took about 4 hours (+4 to
build the indexes and vacuum analyse). I dont know why the cluster is
so much slower...

My "SELECT * FROM <table> LIMIT 1;" is now fast.

Thanks for the help!

As a side note, I think the behavior of the Database is a bit
counter-intuitive. If I were to do a:

UPDATE <table> SET i = i + 1;

My table will "leak" 10Gb of disk space, make queries extreamly slow,
and require taking the database off-line for 12 hours to fix it!

Both the VACUUM and the UPDATE manual should be updated to say that this
is whats happening. If I do my above query 100 times, will I be leaking
a Terrabyte of disk space?

Will increasing my max_fsm_pages to 2,000,000 solve my problem?

dave

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Josh Berkus 2004-05-27 17:02:39 Re: tablespaces and DB administration
Previous Message Tom Lane 2004-05-27 13:29:46 Re: SELECT * FROM <table> LIMIT 1; is really slow