Re: [HACKERS] SELECT * FROM LIMIT 1; is really slow David Blasby Thu, 27 May 2004 09:30:41 -0700 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 ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) Previous message View by thread View by date Next message [HACKERS] SELECT * FROM <table> LIMIT 1; is... David Blasby Re: [HACKERS] SELECT * FROM <table> LIM... Gaetano Mendola Re: [HACKERS] SELECT * FROM <table>... David Blasby Re: [HACKERS] SELECT * FROM <table... Tom Lane Re: [HACKERS] SELECT * FROM <t... David Blasby Re: [HACKERS] SELECT * FROM ... Tom Lane Re: [HACKERS] SELECT * F... Paul Ramsey Re: [HACKERS] SELECT * FROM ... David Blasby Re: [HACKERS] SELECT * FROM <t... pgsql Re: [HACKERS] SELECT * FROM <table> LIM... Tom Lane Re: [HACKERS] SELECT * FROM <table>... Christopher Kings-Lynne Re: [HACKERS] SELECT * FROM <table... Tom Lane Re: [HACKERS] SELECT * FROM <t... Dennis Bjorklund Re: [HACKERS] SELECT * FROM ... Tom Lane Re: [HACKERS] SELECT * FROM <table>... Manfred Koizar Re: [HACKERS] SELECT * FROM <table... Tom Lane Re: [HACKERS] SELECT * FROM <t... Manfred Koizar Re: [HACKERS] SELECT * FROM ... Alvaro Herrera Reply via email to Search the site The Mail Archive home pgsql-hackers - all messages pgsql-hackers - about the list Expand Previous message Next message The Mail Archive home Add your mailing list FAQ Support Privacy 40B616CE.7090907@refractions.net

[David Blasby]

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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])