--before vacuum EXPLAIN ANALYZE select oi.id from order_items oi inner join clients_orders co on oi.order_id = co.id; 'Hash Join (cost=780.55..1908023.16 rows=1027457 width=4) (actual time=63.506..85607.003 rows=33768 loops=1)' ' Hash Cond: (oi.order_id = co.id)' ' -> Seq Scan on order_items oi (cost=0.00..1558536.52 rows=33843152 width=8) (actual time=0.005..69718.563 rows=33909137 loops=1)' ' -> Hash (cost=480.80..480.80 rows=23980 width=4) (actual time=13.072..13.072 rows=23980 loops=1)' ' Buckets: 4096 Batches: 1 Memory Usage: 844kB' ' -> Seq Scan on clients_orders co (cost=0.00..480.80 rows=23980 width=4) (actual time=0.006..6.570 rows=23980 loops=1)' 'Total runtime: 85613.391 ms' vacuum analyze verbose clients_orders INFO: vacuuming "public.clients_orders" INFO: index "idxt_order_id" now contains 23980 row versions in 68 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.03 sec. INFO: "clients_orders": found 0 removable, 23980 nonremovable row versions in 241 out of 241 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.00u sec elapsed 0.13 sec. INFO: vacuuming "pg_toast.pg_toast_5780808" INFO: index "pg_toast_5780808_index" now contains 0 row versions in 1 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_toast_5780808": found 0 removable, 0 nonremovable row versions in 0 out of 0 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.00u sec elapsed 0.00 sec. INFO: analyzing "public.clients_orders" INFO: "clients_orders": scanned 241 of 241 pages, containing 23980 live rows and 0 dead rows; 23980 rows in sample, 23980 estimated total rows Query returned successfully with no result in 4703 ms. vacuum analyze verbose order_items; INFO: vacuuming "public.order_items" INFO: scanned index "order_items_pkey" to remove 274035 row versions DETAIL: CPU 0.31s/1.21u sec elapsed 365.55 sec. INFO: scanned index "idx_order_items_order_id_odd" to remove 274035 row versions DETAIL: CPU 0.53s/5.14u sec elapsed 76.16 sec. INFO: scanned index "idx_order_items_order_id_is_taxable_odd" to remove 274035 row versions DETAIL: CPU 0.23s/1.80u sec elapsed 26.03 sec. INFO: scanned index "idx_order_items_id_is_taxable_odd" to remove 274035 row versions DETAIL: CPU 0.52s/5.09u sec elapsed 76.89 sec. INFO: "order_items": removed 274035 row versions in 65949 pages DETAIL: CPU 0.08s/0.13u sec elapsed 105.31 sec. INFO: index "order_items_pkey" now contains 33909764 row versions in 648624 pages DETAIL: 265266 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "idx_order_items_order_id_odd" now contains 33909823 row versions in 105751 pages DETAIL: 245002 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "idx_order_items_order_id_is_taxable_odd" now contains 12328637 row versions in 38531 pages DETAIL: 105571 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "idx_order_items_id_is_taxable_odd" now contains 33909975 row versions in 106146 pages DETAIL: 256687 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "order_items": found 3258 removable, 7587867 nonremovable row versions in 216337 out of 1220105 pages DETAIL: 15 dead row versions cannot be removed yet. There were 3631594 unused item pointers. 0 pages are entirely empty. CPU 1.69s/13.40u sec elapsed 769.71 sec. INFO: vacuuming "pg_toast.pg_toast_158461" INFO: index "pg_toast_158461_index" now contains 0 row versions in 1 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_toast_158461": found 0 removable, 0 nonremovable row versions in 0 out of 0 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.00u sec elapsed 0.00 sec. INFO: analyzing "public.order_items" INFO: "order_items": scanned 30000 of 1220105 pages, containing 832401 live rows and 15 dead rows; 30000 rows in sample, 33853887 estimated total rows Query returned successfully with no result in 846343 ms. --after vacuum EXPLAIN ANALYZE select oi.id from order_items oi inner join clients_orders co on oi.order_id = co.id; 'Hash Join (cost=780.55..1908455.96 rows=1049265 width=4) (actual time=116.245..99165.640 rows=33768 loops=1)' ' Hash Cond: (oi.order_id = co.id)' ' -> Seq Scan on order_items oi (cost=0.00..1558643.88 rows=33853888 width=8) (actual time=2.742..82990.729 rows=33909690 loops=1)' ' -> Hash (cost=480.80..480.80 rows=23980 width=4) (actual time=14.520..14.520 rows=23980 loops=1)' ' Buckets: 4096 Batches: 1 Memory Usage: 844kB' ' -> Seq Scan on clients_orders co (cost=0.00..480.80 rows=23980 width=4) (actual time=0.007..7.619 rows=23980 loops=1)' 'Total runtime: 99171.801 ms'