running two copies of: drop table test_data; create table test_data(f1 int); insert into test_data values (random() * 100); insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; insert into test_data select random() * 100 from test_data; create index test_index on test_data(f1); vacuum verbose analyze test_data; checkpoint; -- force nestloop indexscan plan set enable_seqscan to 0; set enable_mergejoin to 0; set enable_hashjoin to 0; explain select count(*) from test_data a, test_data b, test_data c where a.f1 = b.f1 and b.f1 = c.f1; select count(*) from test_data a, test_data b, test_data c where a.f1 = b.f1 and b.f1 = c.f1; -- Result from 1 of 2 executions: d01.int> psql -U test; Welcome to psql 7.4.5, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit test=> \i test.sql DROP TABLE CREATE TABLE INSERT 11296941 1 INSERT 11296942 1 INSERT 0 2 INSERT 0 4 INSERT 0 8 INSERT 0 16 INSERT 0 32 INSERT 0 64 INSERT 0 128 INSERT 0 256 INSERT 0 512 INSERT 0 1024 INSERT 0 2048 INSERT 0 4096 INSERT 0 8192 INSERT 0 16384 INSERT 0 32768 psql:test.sql:23: ERROR: relation "test_index" already exists psql:test.sql:25: INFO: vacuuming "public.test_data" psql:test.sql:25: INFO: index "test_index" now contains 65536 row versions in 198 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 1.74 sec. psql:test.sql:25: INFO: "test_data": found 0 removable, 65536 nonremovable row versions in 289 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.01u sec elapsed 1.75 sec. psql:test.sql:25: INFO: analyzing "public.test_data" psql:test.sql:25: INFO: "test_data": 289 pages, 3000 rows sampled, 65603 estimated total rows VACUUM CHECKPOINT SET SET SET QUERY PLAN ------------------------------------------------------------------------------------------------------- Aggregate (cost=50844039917.94..50844039917.94 rows=1 width=0) -> Nested Loop (cost=0.00..50773112976.11 rows=28370776731 width=0) -> Nested Loop (cost=0.00..77053124.16 rows=43119685 width=8) -> Index Scan using test_index on test_data b (cost=0.00..2074.77 rows=65536 width=4) -> Index Scan using test_index on test_data c (cost=0.00..1167.59 rows=649 width=4) Index Cond: ("outer".f1 = c.f1) -> Index Scan using test_index on test_data a (cost=0.00..1167.59 rows=649 width=4) Index Cond: ("outer".f1 = a.f1) (8 rows) ^CCancel request sent psql:test.sql:38: ERROR: canceling query due to user request test=> --- d01.int> vmstat 5 procs memory page disks faults cpu r b w avm fre flt re pi po fr sr da0 fd0 in sy cs us sy id 0 0 0 196360 258572 2336 0 0 0 1687 1 0 0 722 0 424 5 3 91 0 2 3 208792 249976 9917 0 0 0 1440 0 2 0 720 0 3958 5 4 91 1 0 0 202056 254900 14728 0 0 0 2844 0 12 0 1128 0 5755 9 7 84 3 0 0 199960 252148 13975 0 1 0 3009 0 47 0 1126 0 13410 15 8 77 0 2 3 203568 247276 8469 0 0 0 1603 0 42 0 906 0 8774 26 5 69 0 10 7 225508 232968 10533 0 0 0 1023 0 13 0 710 0 11756 28 765 0 1 4 202724 248416 8847 0 0 0 2374 0 1 0 697 0 8671 30 5 65 0 15 14 245484 220092 11856 0 0 0 1176 0 9 0 917 0 13885 26 8 66 0 20 7 243836 222684 13190 0 0 0 2208 0 7 0 831 0 17925 24 10 67 0 4 7 217928 240192 10879 0 0 0 3026 0 10 0 954 0 14428 28 7 65 0 3 10 223192 236708 10570 0 0 0 2042 0 35 0 872 0 10530 29 6 66 3 12 1 225916 235372 11205 0 0 0 2048 0 6 0 809 0 12919 26 7 67 0 9 8 224092 236468 12562 0 0 0 2116 0 6 0 882 0 11334 26 7 67 4 2 0 208728 246880 9979 0 0 0 2224 0 3 0 758 0 9319 28 6 66 4 5 6 217832 241144 11436 0 0 0 1995 0 9 0 852 0 10787 26 6 67 5 3 4 216452 241404 10989 0 0 0 2256 0 11 0 831 0 12633 27 7 66 1 0 0 198896 255112 10958 0 0 0 2281 0 45 0 855 0 14768 20 7 73 0 0 0 197204 256160 9382 0 0 0 1701 0 6 0 727 0 3634 5 4 91 ^C