On Wed, Sep 29, 2010 at 11:55 AM, Leonardo Francalanci <m_li...@yahoo.it> wrote: > Can someone else test the patch to see if what I found is still valid? > I don't think it makes much sense if I'm the only one that says > "this is faster" :)
I ran a few more performance tests on this patch. Here's what I got for the tests Leonardo posted originally: * 2M rows: 22 seconds for seq. scan, 24 seconds for index scan * 5M rows: 139 seconds for seq. scan, 97 seconds for index scan * 10M rows: 256 seconds seq. scan, 611 seconds for index scan (times are for the cluster operation only, not for the table creations, etc. which took most of the time) I tried a few more tests of creating a table with either 10M or 50M rows, then deleting 90% of the rows and running a cluster. The patch didn't fare so well here: * 10M rows: 84 seconds for seq. scan, 44 seconds for index scan The seq. scan results here were obtained with the patch applied, and without using planner hints (enable_seqscan or enable_indexscan). I added in an ereport() call to check that use_sort was actually true. The index scan results were obtained without the patch applied. The SQL file I used is attached. So I think there are definitely cases where this patch helps, but it looks like a seq. scan is being chosen in some cases where it doesn't help. Test machine: MacBook Pro laptop, C2D 2.53 GHz, 4GB RAM. Settings: shared_buffers = 16MB, work_mem and maintenance_work_mem set from the SQL scripts. Josh
\timing on BEGIN; set work_mem='100MB'; set maintenance_work_mem='100MB'; CREATE TABLE mybloat ( myid int, name text ); INSERT INTO mybloat (myid, name) SELECT a, a::text FROM generate_series(1,10000000) AS a; ALTER TABLE mybloat ADD CONSTRAINT myid_pkey PRIMARY KEY (myid); DELETE FROM mybloat WHERE RANDOM() < 0.9; ANALYZE mybloat; select attname, correlation from pg_stats where tablename='mybloat'; cluster verbose mybloat using myid_pkey; drop table mybloat; COMMIT;
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
