>>> I wrote: > I tried the same run on 8.4devel and it is still running after > 20 minutes. I will let it cook for a while. It's now been an hour and 30 minutes; so, while 8.4 does a much better job of estimating how many rows will be returned, the plan it generates is much slower for this query. FWIW I'll attach the results of vmstat 1 below. The machine a replication target and is receiving a little over 100 database transactions per second, but is otherwise fairly idle, except for this long-running query. -Kevin
procs -----------memory---------- ---swap-- -----io---- -system-- -----cpu------ r b swpd free buff cache si so bi bo in cs us sy id wa st 1 1 31852 582532 23520 127450520 0 0 456 1324 715 4768 8 0 90 2 0 1 0 31852 582228 23520 127450520 0 0 112 932 812 5426 8 0 91 1 0 2 0 31852 581736 23520 127450520 0 0 248 1014 791 5389 8 0 90 1 0 1 0 31852 580744 23528 127450512 0 0 184 1874 832 5340 8 0 91 1 0 1 1 31852 578284 23528 127451536 0 0 640 1884 1017 11050 11 1 86 3 0 1 0 31852 577852 23528 127451536 0 0 136 1536 1072 8095 8 1 90 1 0 1 0 31852 577484 23528 127451536 0 0 40 642 617 5144 8 0 92 0 0 2 2 31852 577244 23528 127451536 0 0 224 1178 921 8011 8 0 90 1 0 1 1 31852 576520 23528 127452568 0 0 664 2674 1031 7115 8 0 89 2 0 3 1 31852 576280 23536 127452560 0 0 184 863 683 4384 7 0 92 1 0 1 0 31852 575836 23536 127452560 0 0 96 636 683 4014 7 0 92 0 0 1 0 31852 575404 23536 127452560 0 0 488 1388 803 8010 9 0 89 2 0 1 0 31852 575404 23536 127452560 0 0 64 559 510 2399 7 0 93 0 0 3 2 31852 574736 23536 127453584 0 0 360 20347 1278 6053 7 1 90 2 0 3 0 31852 573636 23544 127455632 0 0 584 6449 1463 9942 9 1 87 3 0 1 0 31852 595336 23536 127435088 0 0 424 1961 1142 19773 9 1 89 1 0 1 0 31852 594552 23536 127436112 0 0 464 1607 1082 8216 9 1 90 1 0 1 0 31852 594432 23536 127436112 0 0 144 1308 1041 9862 9 1 90 1 0 2 0 31852 593876 23536 127436112 0 0 128 3829 872 4400 7 1 92 1 0 1 1 31852 593080 23544 127436104 0 0 608 1025 792 3529 7 0 92 1 0 1 0 31852 592708 23544 127436104 0 0 264 1111 908 5836 8 1 90 1 0 1 0 31852 592464 23544 127436104 0 0 264 1164 888 6152 8 1 90 1 0 1 0 31852 592528 23544 127436104 0 0 8 628 756 4577 7 0 92 0 0 1 1 31852 592344 23544 127436104 0 0 108 13982 978 4853 7 1 91 1 0 1 0 31852 592164 23544 127436104 0 0 176 629 527 2953 7 0 92 1 0 1 0 31852 591652 23552 127437120 0 0 40 680 727 4434 7 1 92 0 0 1 0 31852 590868 23552 127437120 0 0 264 1519 1129 10780 8 1 90 1 0 5 0 31852 590932 23552 127437120 0 0 56 543 617 5955 7 0 92 1 0 1 0 31852 590448 23552 127437120 0 0 288 3863 868 4978 7 0 91 2 0 1 0 31852 590404 23552 127437120 0 0 80 753 653 3864 7 0 92 0 0 2 1 31852 588700 23552 127438152 0 0 672 1701 1027 6157 8 0 91 1 0 1 1 31852 587436 23560 127439168 0 0 264 11983 2318 15896 8 1 85 6 0 1 0 31852 586896 23560 127441232 0 0 392 1155 710 4815 8 0 90 2 0 1 0 31852 586232 23560 127441232 0 0 664 1962 946 6980 9 1 89 2 0 1 0 31852 585860 23560 127441232 0 0 64 994 937 9339 8 0 91 1 0 1 0 31852 584004 23560 127441232 0 0 80 816 757 10553 8 0 91 1 0 1 0 31852 583768 23568 127441216 0 0 280 1546 880 6011 8 0 90 2 0 1 0 31852 582320 23568 127442248 0 0 648 1723 1080 5712 8 0 90 1 0 1 0 31852 580784 23568 127442248 0 0 408 2394 1471 9593 9 1 88 2 0 1 0 31852 580420 23568 127442248 0 0 328 1698 1151 8913 9 1 88 2 0
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
