-- Table: tb_svclevel_dtl
CREATE TABLE "tb_svclevel_dtl" (
"orderid" int4 NOT NULL,
"outletid" int4 NOT NULL,
"outletname" varchar(100) NOT NULL,
"outlettype" varchar(20) NOT NULL,
"productid" int4 NOT NULL
);
-- Table: tb_svclevel_dtl index
CREATE INDEX svclevel_dtl_idx ON tb_svclevel_dtl USING btree (orderid int4_ops);
CREATE INDEX tb_svclevel_dtl_idx1 ON tb_svclevel_dtl USING btree (orderid int4_ops, outletid int4_ops, productid int4_ops);
CREATE INDEX tb_svclevel_dtl_idx2 ON tb_svclevel_dtl USING btree (outletid int4_ops, productid int4_ops);
CREATE INDEX tb_svclevel_dtl_idx3 ON tb_svclevel_dtl USING btree (productid int4_ops);
CREATE INDEX tb_svclevel_dtl_idx4 ON tb_svclevel_dtl USING btree (outletid int4_ops);
CREATE INDEX tb_svclevel_dtl_idx5 ON tb_svclevel_dtl USING hash (orderid int4_ops);
-- Table: tb_orig_order_dtl
CREATE TABLE "tb_orig_order_dtl" (
"orderid" int4 NOT NULL,
"outletid" int4 NOT NULL,
"productid" int4 NOT NULL,
CONSTRAINT "pk_tb_orig_order_dtl" PRIMARY KEY ("orderid", "outletid", "productid")
);
-- Query in question
SELECT * FROM
tb_orig_order_dtl v
LEFT OUTER JOIN
tb_svclevel_dtl s
on
v.orderid = s.orderid
where
v.orderid=123;
-- Explain result
Merge Join (cost=425762.47..451467.23 rows=901889819 width=220)
-> Index Scan using pk_tb_orig_order_dtl on tb_orig_order_dtl v (cost=0.00..4430.75 rows=54754 width=48)
-> Sort (cost=425762.47..425762.47 rows=1647167 width=172)
-> Seq Scan on tb_svclevel_dtl s (cost=0.00..60629.67 rows=1647167 width=172)
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE