BUG #14646: performance hint to remove

The following bug has been logged on the website:

Bug reference: 14646
Logged by: Boshomi
Email address: boshomi(at)gmail(dot)com
PostgreSQL version: 9.6.2
Operating system: Linux opensuse
Description:

the order of tables in from clause or where clause can result in different
query plans, with different execution time.

Begin;
--DROP TABLE testjoinperf.tempsmall CASCADE;
--DROP TABLE testjoinperf.tempbig;
/*
CREATE SCHEMA testjoinperf;
CREATE TABLE testjoinperf.tempsmall
(
idsmall serial NOT NULL,
smallrnd double precision,
CONSTRAINT pk_tempsamll PRIMARY KEY (idsmall)
);

CREATE TABLE testjoinperf.tempbig
(
idbig serial NOT NULL,
idsmall integer,
bigrnd double precision,
CONSTRAINT pk_tempbig PRIMARY KEY (idbig),
CONSTRAINT fk_tempsmall FOREIGN KEY (idsmall)
REFERENCES testjoinperf.tempsmall (idsmall) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
);

insert into testjoinperf.tempsmall (idsmall, smallrnd)
select a.a, random() from generate_series(1,50000) as a;

insert into testjoinperf.tempbig (idbig, idsmall, bigrnd)
select b.b, (b.b % 50000)+1 , random() from generate_series(1,1000000) as
b;
analyze testjoinperf.tempsmall;
analyze testjoinperf.tempbig;
analyze;
*/
/*

--fast query big table left
explain analyze select * from testjoinperf.tempbig join
testjoinperf.tempsmall using(idsmall)
where idsmall between 48000 and 49000
limit 100;
*/

/*

-- slow query, small table left.
explain analyze select * from testjoinperf.tempsmall join
testjoinperf.tempbig using(idsmall)
where idsmall between 48000 and 49000
limit 100;
*/
rollback;

/*
faster query
QUERY PLAN
Limit (cost=0.29..137.92 rows=100 width=24) (actual time=6.589..6.896
rows=100 loops=1)
-> Nested Loop (cost=0.29..28656.67 rows=20821 width=24) (actual
time=6.588..6.880 rows=100 loops=1)
-> Seq Scan on tempbig (cost=0.00..20406.00 rows=20821 width=16)
(actual time=6.576..6.609 rows=100 loops=1)
Filter: ((idsmall >= 48000) AND (idsmall <= 49000))
Rows Removed by Filter: 47998
-> Index Scan using pk_tempsamll on tempsmall (cost=0.29..0.39
rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=100)
Index Cond: (idsmall = tempbig.idsmall)
Planning time: 0.272 ms
Execution time: 6.935 ms

slower query
QUERY PLAN
Limit (cost=52.46..153.33 rows=100 width=24) (actual time=13.063..13.123
rows=100 loops=1)
-> Hash Join (cost=52.46..19399.50 rows=19180 width=24) (actual
time=13.062..13.108 rows=100 loops=1)
Hash Cond: (tempbig.idsmall = tempsmall.idsmall)
-> Seq Scan on tempbig (cost=0.00..15406.00 rows=1000000 width=16)
(actual time=0.011..5.361 rows=48098 loops=1)
-> Hash (cost=40.47..40.47 rows=959 width=12) (actual
time=0.406..0.406 rows=1001 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 55kB
-> Index Scan using pk_tempsamll on tempsmall
(cost=0.29..40.47 rows=959 width=12) (actual time=0.019..0.239 rows=1001
loops=1)
Index Cond: ((idsmall >= 48000) AND (idsmall <=
49000))
Planning time: 0.277 ms
Execution time: 13.166 ms

the query are eqal.

it also makes a differnence if the where clause is
where tempbig.idsmall between 48000 and 49000 /* fast */

or

where tempsmall.idsmall between 48000 and 49000 /* slow */

to change the order of tables is a performance hint, and should be
removed.

(Boshomi is my username on german wikipedia (de.wikipedia.org) feel free to
contact me for any question.