We help IT Professionals succeed at work.

Postgresql -- Query not using index

chinhw
chinhw asked
on
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:
can you try explain after disable seqscan ?

set enable_seqscan=false

and please send your postgresql.conf

Author

Commented:
I tried setting enable_seqscan=false as what you have suggested.
However, it is still using Seq Scan on tb_svclevel_dtl.
I have attached the postgresql.conf.
Thanks and appreciate your help.
postgresql.conf.txt
Commented:
how many ram do you have ? is it a dedicated server

effective_cache_size = 1000 -> 8Mo, it's very low, you should be able to put half your memory on a dedicated server


Author

Commented:
Thanks it works!!!