Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Wmt7b-0001Aq-E0 for pgsql-performance@arkaria.postgresql.org; Tue, 20 May 2014 23:06:59 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Wmt7a-0005BU-Li for pgsql-performance@arkaria.postgresql.org; Tue, 20 May 2014 23:06:58 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Wmt7Z-0005BL-Hd for pgsql-performance@postgresql.org; Tue, 20 May 2014 23:06:57 +0000 Received: from 222-154-234-164.adsl.xtra.co.nz ([222.154.234.164] helo=zimbra.mccarthy.co.nz) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Wmt7V-0004oS-Sn for pgsql-performance@postgresql.org; Tue, 20 May 2014 23:06:56 +0000 Received: from localhost (localhost [127.0.0.1]) by zimbra.mccarthy.co.nz (Postfix) with ESMTP id 3CD25F0; Wed, 21 May 2014 11:06:50 +1200 (NZST) Received: from zimbra.mccarthy.co.nz ([127.0.0.1]) by localhost (zimbra.mccarthy.co.nz [127.0.0.1]) (amavisd-new, port 10032) with ESMTP id g9GKN-RwxBbO; Wed, 21 May 2014 11:06:46 +1200 (NZST) Received: from localhost (localhost [127.0.0.1]) by zimbra.mccarthy.co.nz (Postfix) with ESMTP id 8FBF0263; Wed, 21 May 2014 11:06:46 +1200 (NZST) X-Virus-Scanned: amavisd-new at mccarthy.co.nz Received: from zimbra.mccarthy.co.nz ([127.0.0.1]) by localhost (zimbra.mccarthy.co.nz [127.0.0.1]) (amavisd-new, port 10026) with ESMTP id Td1hLhz3v2Fh; Wed, 21 May 2014 11:06:46 +1200 (NZST) Received: from zimbra.mccarthy.co.nz (localhost [127.0.0.1]) by zimbra.mccarthy.co.nz (Postfix) with ESMTP id 5952CF0; Wed, 21 May 2014 11:06:46 +1200 (NZST) Date: Wed, 21 May 2014 11:06:46 +1200 (NZST) From: Geoff Hull To: David Rowley Cc: pgsql-performance Message-ID: <552030819.222866.1400627206245.JavaMail.zimbra@mccarthy.co.nz> In-Reply-To: References: <1362375391.209823.1400474409764.JavaMail.zimbra@mccarthy.co.nz> <897829134.210060.1400474834336.JavaMail.zimbra@mccarthy.co.nz> Subject: Re: View has different query plan than select statement MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_222865_1235599387.1400627206243" X-Originating-IP: [128.222.3.145] X-Mailer: Zimbra 8.0.5_GA_5839 (ZimbraWebClient - FF29 (Win)/8.0.5_GA_5839) Thread-Topic: View has different query plan than select statement Thread-Index: NC1zq6HgoFLjregqo9dYTOCoZf5DnQ== X-Pg-Spam-Score: -0.9 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org ------=_Part_222865_1235599387.1400627206243 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit ----- Original Message ----- From: "David Rowley" To: "Geoff Hull" Cc: "pgsql-performance" 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.hull@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-owner@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? 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_0wEcogi9UEKTw@mail.gmail.com It should apply to current HEAD without too much trouble. Regards David Rowley David, Thank you so much for the helpful (and speedy) reply. I talked to our developer Tim about this, and your reply exactly described his problem. I downloaded the source for the 9.4beta1 version and used your patch. I compiled it, etc, then we ran Tim's test and it worked perfectly - it now uses the index in the view: SELECT: "WindowAgg (cost=8.31..8.34 rows=1 width=50) (actual time=0.043..0.043 rows=0 loops=1)" " Buffers: shared hit=5" " -> Sort (cost=8.31..8.32 rows=1 width=50) (actual time=0.041..0.041 rows=0 loops=1)" " Sort Key: history_id" " Sort Method: quicksort Memory: 25kB" " Buffers: shared hit=5" " -> Index Scan using idx_test_table on test_table (cost=0.29..8.30 rows=1 width=50) (actual time=0.008..0.008 rows=0 loops=1)" " Index Cond: ((id)::text = '"cb05b1cd2659f7cea9436ed20e055df5"'::text)" " Buffers: shared hit=2" "Planning time: 0.188 ms" "Execution time: 0.133 ms" VIEW: "Subquery Scan on v_test_table (cost=8.31..8.35 rows=1 width=65) (actual time=0.030..0.030 rows=0 loops=1)" " Buffers: shared hit=2" " -> WindowAgg (cost=8.31..8.34 rows=1 width=50) (actual time=0.030..0.030 rows=0 loops=1)" " Buffers: shared hit=2" " -> Sort (cost=8.31..8.32 rows=1 width=50) (actual time=0.028..0.028 rows=0 loops=1)" " Sort Key: test_table.history_id" " Sort Method: quicksort Memory: 25kB" " Buffers: shared hit=2" " -> Index Scan using idx_test_table on test_table (cost=0.29..8.30 rows=1 width=50) (actual time=0.012..0.012 rows=0 loops=1)" " Index Cond: ((id)::text = '"cb05b1cd2659f7cea9436ed20e055df5"'::text)" " Buffers: shared hit=2" "Planning time: 0.216 ms" "Execution time: 0.120 ms" Lovely! We're looking forward to PostgreSQL 9.5. Thanks, Geoff and Tim ------=_Part_222865_1235599387.1400627206243 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable


<= div style=3D"color:#000;font-weight:normal;font-style:normal;text-decoratio= n:none;font-family:Helvetica,Arial,sans-serif;font-size:12pt;">From: "David Rowley" <dgrowleyml@gmail.com>
To: "Geoff Hull" <= ;geoff.hull@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 sel= ect statement

On Mon, May 19, 2014 at 4:47 PM, Geoff Hull <geoff.hull@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-owne= r@postgresql.org but without getting a reply.

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

Hi,

I ha= ve created a table 'test_table' and index 'idx_test_table' with a view 'v_t= est_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 t= hat query specific hints are not available in Postgres 9.1 how can I persua= de the view to use the same query plan as the select statement?

Thanks,

Tim


--DROP table test_t= able CASCADE;

-- create test table
CREATE TABLE test_t= able (
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
INSE= RT INTO test_table (id, name) SELECT *, 'Danger Mouse' FROM (SELECT md5(ran= dom()::text) from generate_series(1,10000)) q;

-- collect stats
ANALYZE test_table;


EXPLAIN (= ANALYZE, BUFFERS)
SELECT *
FROM test_table
WHERE id =3D '02b304b1c= 54542570d9f7bd39361f5b4';

"Index Scan using idx_test_tabl= e on test_table (cost=3D0.00..8.27 rows=3D1 width=3D50) (actual time=3D0.02= 1..0.022 rows=3D1 loops=3D1)"
" Index Cond: ((id)::text =3D '02b304b1c54542570d9f7bd39361f5b4'::text)"" Buffers: shared hit=3D3"
"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 h= istory_id), name || 'x') <> name
then name
end as name
FROM = test_table
WHERE id =3D '02b304b1c54542570d9f7bd39361f5b4';

"WindowAgg (cost=3D8.28..8.31 rows=3D1 width=3D50) (actual time=3D0.050= ..0.051 rows=3D1 loops=3D1)"
" Buffers: shared hit=3D3"
" -> Sort = (cost=3D8.28..8.29 rows=3D1 width=3D50) (actual time=3D0.039..0.039 rows=3D= 1 loops=3D1)"
" Sort Key: history_id"
" Sort Method: quicksort Memory: 25kB"
" Buff= ers: shared hit=3D3"
" -> Index Scan using idx_test_table on test_tab= le (cost=3D0.00..8.27 rows=3D1 width=3D50) (actual time=3D0.030..0.031 rows= =3D1 loops=3D1)"
" Index Cond: ((id)::text =3D '02b304b1c54542570d9f7bd39361f5b4'::text)"" Buffers: shared hit=3D3"
"Total runtime: 0.102 ms"

=
--DROP VIEW v_test_table;

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


-- Query via view with bad plan
<= br>
EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM v_test_table
WHERE id =3D '02b304b1c54542570d9f7bd39361= f5b4';

"Subquery Scan on v_test_table (cost=3D868.39..124= 3.39 rows=3D50 width=3D65) (actual time=3D26.115..33.327 rows=3D1 loops=3D1= )"
" Filter: ((v_test_tab= le.id)::text =3D '02b304b1c54542570d9f7bd39361f5b4'::text)"
" Buffer= s: shared hit=3D104, temp read=3D77 written=3D77"
" -> WindowAgg (cos= t=3D868.39..1118.39 rows=3D10000 width=3D50) (actual time=3D26.022..32.519 rows=3D10000 loops=3D1= )"
" Buffers: shared hit=3D104, temp read=3D77 written=3D77"
" -> Sort (= cost=3D868.39..893.39 = rows=3D10000 width=3D50) (actual time=3D26.013..27.796 rows=3D10000 loops= =3D1)"
" Sort Key: test_table.i= d, test_table.history_id"
" Sort Method: external merge Disk: 608kB"=
" Buffers: shared hit=3D104, temp read=3D77 written=3D77"
" -> Seq Scan on test_table (cost=3D0.00..204.00 rows=3D10000 width=3D50= ) (actual time=3D0.010..1.804 rows=3D10000 loops=3D1)"
" Buffers: shared= hit=3D104"
"Total runtime: 33.491 ms"


How can I g= et 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 inde= x 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 stri= ct code which completely disallows pushdowns of where clauses into sub quer= ies that contain windowing functions...

In your case, because you have this id in your partitio= n 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 restricti= on. it unfortunately missed the boat for 9.4, but with any luck it will mak= e it into 9.5. If you're up for compiling postgres from source, then you ca= n test the patch out:


It should apply to current HEAD without too much troubl= e.

Regards

David Rowley&n= bsp;
 

David,
Thank you so much for the helpful (and speedy) reply.
=

I talked to our developer Tim about this, and your repl= y exactly described his problem.

I downloaded = the source for the 9.4beta1 version and used your patch. I compiled it, etc= , then we ran Tim's test and it worked perfectly - it now uses the index in= the view:

SELECT:

"WindowAgg  (cost=3D8.31..8.34 rows=3D1 width=3D50) (actual time=3D0= .043..0.043 rows=3D0 loops=3D1)"
"  Buffers: shared hit=3D5"
"&n= bsp; ->  Sort  (cost=3D8.31..8.32 rows=3D1 width=3D50) (actual= time=3D0.041..0.041 rows=3D0 loops=3D1)"
"     = ;   Sort Key: history_id"
"      = ;  Sort Method: quicksort  Memory: 25kB"
"   &n= bsp;    Buffers: shared hit=3D5"
"    = ;    ->  Index Scan using idx_test_table on test_tab= le  (cost=3D0.29..8.30 rows=3D1 width=3D50) (actual time=3D0.008..0.00= 8 rows=3D0 loops=3D1)"
"        =       Index Cond: ((id)::text =3D '"cb05b1cd2659f7= cea9436ed20e055df5"'::text)"
"       =        Buffers: shared hit=3D2"
"Planning = time: 0.188 ms"
"Execution time: 0.133 ms"

VIEW:

"Subquery Scan on v_test_table  (cost=3D8.31..= 8.35 rows=3D1 width=3D65) (actual time=3D0.030..0.030 rows=3D0 loops=3D1)"<= br>"  Buffers: shared hit=3D2"
"  ->  WindowAgg  = (cost=3D8.31..8.34 rows=3D1 width=3D50) (actual time=3D0.030..0.030 rows=3D= 0 loops=3D1)"
"        Buffers: share= d hit=3D2"
"        ->  Sort&= nbsp; (cost=3D8.31..8.32 rows=3D1 width=3D50) (actual time=3D0.028..0.028 r= ows=3D0 loops=3D1)"
"        &nb= sp;     Sort Key: test_table.history_id"
" &nbs= p;            Sort M= ethod: quicksort  Memory: 25kB"
"     &nbs= p;        Buffers: shared hit=3D2"
"&= nbsp;           &nbs= p; ->  Index Scan using idx_test_table on test_table  (cost=3D= 0.29..8.30 rows=3D1 width=3D50) (actual time=3D0.012..0.012 rows=3D0 loops= =3D1)"
"          &nbs= p;         Index Cond: ((id)::text = =3D '"cb05b1cd2659f7cea9436ed20e055df5"'::text)"
"   &nbs= p;            &= nbsp;   Buffers: shared hit=3D2"
"Planning time: 0.216 ms"
= "Execution time: 0.120 ms"

Lovely!

We're looking forward to Po= stgreSQL 9.5.

Thanks,
Geoff and Tim

------=_Part_222865_1235599387.1400627206243--