What I don't understand is the part where you talking about disabling hash
joins:
> * result: planner replaced hash join is replaced by merge join
> * execution time: 0.84ms !
> * NOTE: curiously planner is expecting to execute this query in 0.29ms
- so it's supposed from its logic to be faster, so why this plan is not used
from the beginning???...
>
> Sort (cost=4562.83..4568.66 rows=2329 width=176) (actual
time=0.237..0.237 rows=20 loops=1)
> Sort Key: h.horder
> Sort Method: quicksort Memory: 30kB
> -> Merge Join (cost=4345.89..4432.58 rows=2329 width=176)
(actual time=0.065..0.216 rows=20 loops=1)
> Merge Cond: (s.ref = h.ref_stat)
> -> Index Scan using stat_ref_idx on stat s
(cost=0.00..49.25 rows=1000 width=45) (actual time=0.018..0.089 rows=193
loops=1)
> -> Sort (cost=4345.89..4351.72 rows=2329 width=135)
(actual time=0.042..0.043 rows=20 loops=1)
> Sort Key: h.ref_stat
> Sort Method: quicksort Memory: 30kB
> -> Index Scan using history_ref_idx on history h
(cost=0.00..4215.64 rows=2329 width=135) (actual time=0.012..0.025 rows=20
loops=1)
> Index Cond: (ref_object = '0000000001'::bpchar)
> Total runtime: 0.288 ms
> (12 rows)
The explain analyze ran the query in 0.288 ms. That is the actual time it
took to run the query on the server. It is not an estimate of the time.
You measured 0.84 ms to run the query, which seems to imply either a problem
in one of the timing methods or that 66% of your query execution time is
sending the results to the client. I'm curious how you did you execution
time measurements.
Dave