CREATE TABLE clients_orders
(
  id integer NOT NULL,
  merchant_id integer NOT NULL,
  ordered_at timestamp without time zone NOT NULL,
  is_valid boolean  NOT NULL,
  order_status_type character varying(500) NOT NULL,
  shipping_country_tax_amount numeric NOT NULL,
  shipping_state_tax_amount numeric NOT NULL,
  grand_total numeric NOT NULL
)
WITH (
  OIDS=FALSE
);
ALTER TABLE clients_orders OWNER TO lawrencec;

-- DROP INDEX idxt_order_id;
CREATE INDEX idxt_order_id
  ON clients_orders
  USING btree
  (id);



CREATE TABLE order_items
(
  id integer NOT NULL,
  order_id integer NOT NULL,
  product_id integer,
  quantity integer NOT NULL,
  sku character varying(255),
  product_name character varying(200) NOT NULL,
  product_type_id integer,
  unit_price numeric NOT NULL,
  is_recurring boolean NOT NULL DEFAULT false,
  is_taxable boolean NOT NULL DEFAULT false,
  is_commissionable boolean NOT NULL DEFAULT false,
  created_at timestamp without time zone NOT NULL DEFAULT now(),
  modified_at timestamp without time zone NOT NULL DEFAULT now(),
  created_from_ip character varying(15) NOT NULL DEFAULT '127.0.0.1'::character varying,
  modified_from_ip character varying(15) NOT NULL DEFAULT '127.0.0.1'::character varying,
  vg_client_id character varying(20),
  bundle_id integer,
  commission_total numeric NOT NULL DEFAULT 0.0,
  sync_order_discount numeric NOT NULL DEFAULT 0.0,
  sync_order_coupon numeric NOT NULL DEFAULT 0.0,
  CONSTRAINT order_items_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE order_items OWNER TO dbo;


-- DROP INDEX idx_order_items_id_is_taxable;
CREATE INDEX idx_order_items_id_is_taxable
  ON order_items
  USING btree
  (id, is_taxable)
  WITH (FILLFACTOR=80);

-- DROP INDEX idx_order_items_order_id_is_taxable;
CREATE INDEX idx_order_items_order_id_is_taxable
  ON order_items
  USING btree
  (order_id, is_taxable)
  WITH (FILLFACTOR=80)
  WHERE is_taxable = true;


-- DROP INDEX idx_order_items_order_id;
CREATE INDEX idx_order_items_order_id
  ON order_items
  USING btree
  (order_id)
  WITH (FILLFACTOR=80);




--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" 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" 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" 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" 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" 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" 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'
