> 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
I don't have time right now to run more tests, I'll try to make some by
next week.
Would it mean that doing:
create table p as select * from atable order by akey
(where akey is random distributed)
with 5M rows is faster with enable_seqscan=0 and
enable_indexscan=1??? That would be weird, especially on a
laptop hard drive! (assuming there's a reasonable amount of
memory set in work_mem/maintenance_work_mem)
> 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
[...]
> 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.
Again: what would the planner choose in that case for a:
create table p as select * from mybloat order by myid
???
I guess that if the planner makes a wrong choice in this case (that is,
seq scan + sort instead of index scan) there's no way for "cluster" to
behave in a different way. If, on the contrary, the "create table..." uses
the right plan, and cluster doesn't, we have a problem in the patch.
Am I right?