Re: [PERFORM] Performance of CLUSTER

2012-06-11 Thread Jeff Davis
On Mon, 2012-06-11 at 08:42 -0500, Shaun Thomas wrote: > On 06/10/2012 03:20 AM, Mark Thornton wrote: > > > 4. 3980922 rows, 1167MB, 276s > > 5. 31843368 rows, 9709MB, ~ 10 hours > > Just judging based on the difference between these two, it would appear > to be from a lot of temp space thrashin

Re: [PERFORM] pg_dump and thousands of schemas

2012-06-11 Thread Jeff Janes
On Sun, Jun 10, 2012 at 4:47 PM, Jeff Janes wrote: > On Wed, May 30, 2012 at 2:06 AM, Tatsuo Ishii wrote: >>> Yeah, Jeff's experiments indicated that the remaining bottleneck is lock >>> management in the server.  What I fixed so far on the pg_dump side >>> should be enough to let partial dumps r

Re: [PERFORM] Performance of CLUSTER

2012-06-11 Thread Shaun Thomas
On 06/11/2012 09:25 AM, Mark Thornton wrote: Certainly not --- the server only has 5GB of memory. Nevertheless I don't expect quadratic behaviour for CLUSTER (n log n would be my expected time). And there it is. :) Since that's the case, *DO NOT* create the symlink from pgsql_tmp to /dev/shm

Re: [PERFORM] Performance of CLUSTER

2012-06-11 Thread Shaun Thomas
On 06/11/2012 09:02 AM, Mark Thornton wrote: I didn't think the process was using even the 500m it ought to have had available, whereas creating an index did appear to use that much. Note though that I didn't stay up all night watching it! You'd be surprised. If you look in your base/pgsql_tmp

Re: [PERFORM] Performance of CLUSTER

2012-06-11 Thread Mark Thornton
On 11/06/12 14:52, Shaun Thomas wrote: On 06/11/2012 08:46 AM, Mark Thornton wrote: 500m --- though isn't clear if cluster uses maintenance memory or the regular work memory. I could readily use a higher value for maintenance_work_mem. For an operation like that, having a full GB wouldn't hur

[PERFORM] postgres clustering interactions with pg_dump

2012-06-11 Thread Fitch, Britt
Hi, I have a table that I am clustering on an index. I am then dumping that table via pg_dump -Fc and loading it into another database via pg_restore. It is unclear to me though if the clustering I did in the original database is preserved during the dump & restore or if I would still need to per

Re: [PERFORM] Performance of CLUSTER

2012-06-11 Thread Shaun Thomas
On 06/10/2012 03:20 AM, Mark Thornton wrote: 4. 3980922 rows, 1167MB, 276s 5. 31843368 rows, 9709MB, ~ 10 hours Just judging based on the difference between these two, it would appear to be from a lot of temp space thrashing. An order of magnitude more rows shouldn't take over 100x longer to

Re: [PERFORM] how to change the index chosen in plan?

2012-06-11 Thread Rural Hunter
于 2012/6/11 20:07, Kevin Grittner 写道: Rural Hunter wrote: 于 2012/6/9 22:39, Kevin Grittner 写道: You neglected to mention the LIMIT clause in your earlier presentation of the problem. A LIMIT can have a big impact on plan choice. Is the LIMIT 10 part of the actual query you want to optimize?

Re: [PERFORM] how to change the index chosen in plan?

2012-06-11 Thread Kevin Grittner
Rural Hunter wrote: > 于 2012/6/9 22:39, Kevin Grittner 写道: >> You neglected to mention the LIMIT clause in your earlier >> presentation of the problem. A LIMIT can have a big impact on plan >> choice. Is the LIMIT 10 part of the actual query you want to >> optimize? Either way it would be helpfu

Re: [PERFORM] Tablespaces and query planning

2012-06-11 Thread Albe Laurenz
Cédric Villemain wrote: > > - The statistics gathered by ANALYZE are independent of the tablespace > > containing the table. > > yes. > > > - The tablespace containing the table has no influence on query planning > > unless seq_page_cost or random_page_cost has been set on the > > tablespace.