We help IT Professionals succeed at work.
Get Started

Postgresql -- Query not using index

chinhw
chinhw asked
on
843 Views
Last Modified: 2012-06-27
Hi,
I have an old project that is using Postgresql version 7.1.3.
I have a problem where the query is not using the index.  It is doing a table scan instead. The column are of the same data type. I  also tried different type of index and it is still doing a table scan.
Any body can help to explain why this is so and if there is any way to resolve this problem.
One of the table could grow to million of record.
-- 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)

Open in new window

Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

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.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE