I have an unusual execution plan below. I am executing a query using the
primary key and am getting sequential scan. How can I force the use of
the unique index?

scott=> explain analyze
scott-> select ename,job from emp where empno=8000;
QUERY
PLAN
----------------------------------------------------------------------------------------------
Seq Scan on emp (cost=0.00..1.18 rows=1 width=74) (actual
time=0.053..0.054 rows=1 loops=1)
Filter: (empno = 8000)
Total runtime: 0.105 ms
(3 rows)

scott=> \d emp
Table "public.emp"
Column | Type | Modifiers
----------+-----------------------------+-----------
empno | smallint | not null
ename | character varying(10) | not null
job | character varying(9) |
mgr | smallint |
hiredate | timestamp without time zone |
sal | double precision |
comm | double precision |
deptno | smallint |
Indexes:
"emp_pkey" PRIMARY KEY, btree (empno)
"emp_ename_id" btree (ename)
"ind_emp_deptno" btree (deptno)
Foreign-key constraints:
"fk_deptno" FOREIGN KEY (deptno) REFERENCES dept(deptno)
Triggers:
_scott_cluster_logtrigger_1 AFTER INSERT OR DELETE OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE _scott_cluster.logtrigger
('_scott_cluster', '1', 'kvvvvvvv')
log_trg AFTER INSERT ON emp FOR EACH ROW EXECUTE PROCEDURE fn_log_trg
()

scott=>


If I fill the table with a multitude of dummy rows instead of the
expected 14, the plan is correct. Is there any method in PostgreSQL which
I can use to force a desired execution plan?

--
http://mgogala.byethost5.com

Mladen Gogala <no (AT) email (DOT) here.invalid> wrote:
You haven't enough rows in the table, a seq-scen are cheaper than an
index-scan + read the table.

test=*# create table foo (id int primary key, value text);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
CREATE TABLE
Zeit: 242,961 ms
test=*# insert into foo values (1, 'test');
INSERT 0 1
Zeit: 1,110 ms
test=*# analyse foo;
ANALYZE
Zeit: 34,911 ms
test=*# explain analyse select * from foo where id=1;
QUERY PLAN
---------------------------------------------------------------------------------------------
Seq Scan on foo (cost=0.00..1.01 rows=1 width=9) (actual time=0.013..0.014 rows=1 loops=1)
Filter: (id = 1)
Total runtime: 0.040 ms
(3 Zeilen)

Zeit: 20,173 ms
test=*# set enable_seqscan=0;
SET
Zeit: 0,142 ms
test=*# explain analyse select * from foo where id=1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Index Scan using foo_pkey on foo (cost=0.00..8.27 rows=1 width=9) (actual time=0.014..0.016 rows=1 loops=1)
Index Cond: (id = 1)
Total runtime: 0.044 ms
(3 Zeilen)

Zeit: 0,350 ms


As you can see, the index-scan is more expensive and need more time.



In short: PG is smart enough to decide the right plan.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

On Sat, 14 Nov 2009 13:02:26 +0100, Andreas Kretschmer wrote:


Yes, I can see that, but the question is if there is room for the human
intervention? In other words, is there a way to influence the plans?
I know that PostgreSQL doesn't have hints but there is probably something
that can be used instead of them?

Loki <loki (AT) asgard (DOT) hr.invalid> wrote:
Sure, you can disable seq-scans:

test=# create table test (i int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
Time: 2.388 ms
test=*# insert into test values (1);
INSERT 0 1
Time: 0.552 ms
test=*# analyse test;
ANALYZE
Time: 0.908 ms
test=*# explain select * from test where i=1;
QUERY PLAN
----------------------------------------------------
Seq Scan on test (cost=0.00..1.01 rows=1 width=4)
Filter: (i = 1)
(2 rows)

Time: 0.371 ms
test=*# set enable_seqscan=false;
SET
Time: 0.104 ms
test=*# explain select * from test where i=1;
QUERY PLAN
----------------------------------------------------------------------
Index Scan using test_pkey on test (cost=0.00..8.27 rows=1 width=4)
Index Cond: (i = 1)
(2 rows)

Time: 0.305 ms


And please consider the costs!


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

On Sat, 14 Nov 2009 20:59:48 +0100, Andreas Kretschmer wrote:

As someone coming from the world of Oracle, I know that no optimizer is
perfect. There are PostgreSQL parameters that can alter the optimizer
decision and favor indexes. One such parameter is "random_page_cost",
with the default value of 4, is basically instructing optimizer that a
cost of an index page is approximately 4 times as large as the cost of a
page begotten through the full table scan. Believe it or not, there is a
similar parameter in the oracle world, called "optimizer_index_cost_adj"
with the approximately the same meaning. Setting this to 1 will make the
index scan be equally as cheap as the sequential scan, significantly
increasing the probability of optimizer choosing the index scan. Lowering
it even further will make optimizer select index if it exists, modifying
the behavior to fit the one of Oracle's rule based optimizer. I believe
that this answers my own question.



--
http://mgogala.freehostia.com

On Sun, 15 Nov 2009 06:32:01 +0000, Mladen Gogala wrote:

The following setting makes the optimizer chose index every single time:

seq_page_cost = 1.0 # measured on an arbitrary scale
#random_page_cost = 4.0 # same scale as above
random_page_cost = 0.1 # same scale as above


scott=> explain select ename,job from emp where empno=7934;
QUERY PLAN
---------------------------------------------------------------------
Index Scan using emp_pkey on emp (cost=0.00..0.77 rows=1 width=13)
Index Cond: (empno = 7934)
(2 rows)


However, now we can start using tricks that were used in Oracle V6 to
force the sequential scan. Notice the "+0" in the where condition.
Optimizer can work on column names only, not on the expressions. This
particular expression does not alter the value but prevents the optimizer
from selecting the index path.

scott=> explain select ename,job from emp where empno+0=7934;
QUERY PLAN
----------------------------------------------------
Seq Scan on emp (cost=0.00..1.21 rows=1 width=13)
Filter: ((empno + 0) = 7934)
(2 rows)


--
http://mgogala.byethost5.com