[PERFORM] View has different query plan than select statement

[Geoff Hull]

I am sending this on behalf of my colleague who tried to post to this list last 
year but without success, then also tried 
pgsql-performance-ow...@postgresql.org but without getting a reply. 

I have recently re-tested this in P/G version 9.3.4 with the same results: 

Hi, 

I have created a table 'test_table' and index 'idx_test_table' with a view 
'v_test_table'. However the query plan used by the view does not use the index 
but when running the select statement itself it does use the index. Given that 
query specific hints are not available in Postgres 9.1 how can I persuade the 
view to use the same query plan as the select statement? 

Thanks, 

Tim 


--DROP table test_table CASCADE; 

-- create test table 
CREATE TABLE test_table ( 
history_id SERIAL, 
id character varying(50) NOT NULL , 
name character varying(50), 
CONSTRAINT test_table_pkey PRIMARY KEY (history_id) 
); 

-- create index on test table 
CREATE INDEX idx_test_table ON test_table (id); 

-- populate test table 
INSERT INTO test_table (id, name) SELECT *, 'Danger Mouse' FROM (SELECT 
md5(random()::text) from generate_series(1,10000)) q; 

-- collect stats 
ANALYZE test_table; 


EXPLAIN (ANALYZE, BUFFERS) 
SELECT * 
FROM test_table 
WHERE id = '02b304b1c54542570d9f7bd39361f5b4'; 

"Index Scan using idx_test_table on test_table (cost=0.00..8.27 rows=1 
width=50) (actual time=0.021..0.022 rows=1 loops=1)" 
" Index Cond: ((id)::text = '02b304b1c54542570d9f7bd39361f5b4'::text)" 
" Buffers: shared hit=3" 
"Total runtime: 0.051 ms" 


-- select statement with good plan 

EXPLAIN (ANALYZE, BUFFERS) 
SELECT id, 
CASE WHEN COALESCE(LAG(name) OVER (PARTITION BY id ORDER BY history_id), name 
|| 'x') <> name 
then name 
end as name 
FROM test_table 
WHERE id = '02b304b1c54542570d9f7bd39361f5b4'; 

"WindowAgg (cost=8.28..8.31 rows=1 width=50) (actual time=0.050..0.051 rows=1 
loops=1)" 
" Buffers: shared hit=3" 
" -> Sort (cost=8.28..8.29 rows=1 width=50) (actual time=0.039..0.039 rows=1 
loops=1)" 
" Sort Key: history_id" 
" Sort Method: quicksort Memory: 25kB" 
" Buffers: shared hit=3" 
" -> Index Scan using idx_test_table on test_table (cost=0.00..8.27 rows=1 
width=50) (actual time=0.030..0.031 rows=1 loops=1)" 
" Index Cond: ((id)::text = '02b304b1c54542570d9f7bd39361f5b4'::text)" 
" Buffers: shared hit=3" 
"Total runtime: 0.102 ms" 


--DROP VIEW v_test_table; 

CREATE OR REPLACE VIEW v_test_table AS 
SELECT id, 
CASE WHEN COALESCE(LAG(name) OVER (PARTITION BY id ORDER BY history_id), name 
|| 'x') <> name 
then name 
end as name 
FROM test_table; 


-- Query via view with bad plan 

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * 
FROM v_test_table 
WHERE id = '02b304b1c54542570d9f7bd39361f5b4'; 

"Subquery Scan on v_test_table (cost=868.39..1243.39 rows=50 width=65) (actual 
time=26.115..33.327 rows=1 loops=1)" 
" Filter: ((v_test_table.id)::text = '02b304b1c54542570d9f7bd39361f5b4'::text)" 
" Buffers: shared hit=104, temp read=77 written=77" 
" -> WindowAgg (cost=868.39..1118.39 rows=10000 width=50) (actual 
time=26.022..32.519 rows=10000 loops=1)" 
" Buffers: shared hit=104, temp read=77 written=77" 
" -> Sort (cost=868.39..893.39 rows=10000 width=50) (actual time=26.013..27.796 
rows=10000 loops=1)" 
" Sort Key: test_table.id, test_table.history_id" 
" Sort Method: external merge Disk: 608kB" 
" Buffers: shared hit=104, temp read=77 written=77" 
" -> Seq Scan on test_table (cost=0.00..204.00 rows=10000 width=50) (actual 
time=0.010..1.804 rows=10000 loops=1)" 
" Buffers: shared hit=104" 
"Total runtime: 33.491 ms" 


How can I get the view to use the same query plan as the select statement?