Re: [PERFORM] View has different query plan than select statement
- Original Message - From: David Rowley dgrowle...@gmail.com To: Geoff Hull geoff.h...@mccarthy.co.nz Cc: pgsql-performance pgsql-performance@postgresql.org Sent: Monday, 19 May, 2014 7:19:17 PM Subject: Re: [PERFORM] View has different query plan than select statement On Mon, May 19, 2014 at 4:47 PM, Geoff Hull geoff.h...@mccarthy.co.nz wrote: 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,1)) 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=1 width=50) (actual time= 26.022..32.519 rows=1 loops=1) Buffers: shared hit=104, temp read=77 written=77 - Sort (cost= 868.39..893.39 rows=1 width=50) (actual time=26.013..27.796 rows=1 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=1 width=50) (actual time=0.010..1.804 rows=1 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? Hi Geoff, Unfortunately the view is not making use of the index due to the presence of the windowing function in the view. I think you would find that if that was removed then the view would more than likely use the index again. The reason for this is that currently the WHERE clause of the outer query is not pushed down into the view due to some overly strict code which completely disallows pushdowns of where clauses into sub queries that contain windowing functions... In your case, because you have this id in your partition by clause, then technically it is possible to push the where clause down into the sub query. I wrote a patch a while back which lifts this restriction. it unfortunately missed the boat for 9.4, but with any luck it will make it into 9.5. If you're up for compiling postgres from source, then you can test the patch out: http://www.postgresql.org/message-id/cahoyfk9ihosarntwc-nj5tphko4wcausd-1c_0wecogi9ue...@mail.gmail.com It should apply to current HEAD without too much trouble. Regards David Rowley
Re: [PERFORM] View has different query plan than select statement
On Mon, May 19, 2014 at 4:47 PM, Geoff Hull geoff.h...@mccarthy.co.nzwrote: 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,1)) 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=1 width=50) (actual time= 26.022..32.519 rows=1 loops=1) Buffers: shared hit=104, temp read=77 written=77 - Sort (cost=868.39..893.39 rows=1 width=50) (actual time=26.013..27.796 rows=1 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=1 width=50) (actual time=0.010..1.804 rows=1 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? Hi Geoff, Unfortunately the view is not making use of the index due to the presence of the windowing function in the view. I think you would find that if that was removed then the view would more than likely use the index again. The reason for this is that currently the WHERE clause of the outer query is not pushed down into the view due to some overly strict code which completely disallows pushdowns of where clauses into sub queries that contain windowing functions... In your case, because you have this id in your partition by clause, then technically it is possible to push the where clause down into the sub query. I wrote a patch a while back which lifts this restriction. it unfortunately missed the boat for 9.4, but with any luck it will make it into 9.5. If you're up for compiling postgres from source, then you can test the patch out: http://www.postgresql.org/message-id/cahoyfk9ihosarntwc-nj5tphko4wcausd-1c_0wecogi9ue...@mail.gmail.com It should apply to current HEAD without too much trouble. Regards David Rowley
[PERFORM] View has different query plan than select statement
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,1)) 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=1 width=50) (actual time=26.022..32.519 rows=1 loops=1) Buffers: shared hit=104, temp read=77 written=77 - Sort (cost=868.39..893.39 rows=1 width=50) (actual time=26.013..27.796 rows=1 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=1 width=50) (actual time=0.010..1.804 rows=1 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?
