X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id ED2C9D1CCDF for ; Fri, 30 Jan 2004 00:32:31 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 89025-01 for ; Thu, 29 Jan 2004 20:32:03 -0400 (AST) Received: from volta.gene.com (unknown [192.12.78.250]) by svr1.postgresql.org (Postfix) with ESMTP id 2BA89D1B45B for ; Thu, 29 Jan 2004 20:31:55 -0400 (AST) Received: from tallac.gene.com (tallac.gene.com [128.137.187.213]) by volta.gene.com (Switch-3.1.0/Switch-3.1.0) with ESMTP id i0U0Veqv018234 for ; Thu, 29 Jan 2004 16:31:40 -0800 (PST) Received: by tallac.gene.com (Postfix, from userid 13922) id C250D4B37C; Thu, 29 Jan 2004 16:31:39 -0800 (PST) Subject: query optimization differs between view and explicit query From: Reece Hart To: pgsql-performance Content-Type: multipart/alternative; boundary="=-nuh+BKthForExGDf52Jv" Message-Id: <1075422699.4062.164.camel@tallac> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.5 Date: Thu, 29 Jan 2004 16:31:39 -0800 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/314 X-Sequence-Number: 5488 --=-nuh+BKthForExGDf52Jv Content-Type: text/plain Content-Transfer-Encoding: 7bit I have a large query which I would like to place in a view. The explicit query is sufficiently fast, but the same query as a view is much slower and uses a different plan. I would appreciate an explanation of why this is, and, more importantly whether/how I might coax the view to use a different plan. The original query: rkh@csb-dev=> select distinct on (AH.p2gblataln_id) AH.p2gblataln_id,H.pseq_id,min(H.pstart) as "pstart", max(H.pstop) as "pstop",A.ident,(A.ident/Q.len::float*100)::int as "pct_ident", sum(H.pstop-H.pstart+1) as "aln_length",H.genasm_id,H.chr,H.plus_strand,min(H.gstart) as "gstart", max(H.gstop) as "gstop" from p2gblathsp H join p2gblatalnhsp AH on H.p2gblathsp_id=AH.p2gblathsp_id join p2gblataln A on AH.p2gblataln_id=A.p2gblataln_id join pseq Q on H.pseq_id=Q.pseq_id where H.pseq_id=76 group by AH.p2gblataln_id,H.pseq_id,H.genasm_id,H.chr,H.plus_strand,A.ident,Q.len \g /dev/null Time: 277.804 ms Now as a view: rkh@csb-dev=> create view v1 as select distinct on (AH.p2gblataln_id) AH.p2gblataln_id,H.pseq_id,min(H.pstart) as "pstart", max(H.pstop) as "pstop",A.ident,(A.ident/Q.len::float*100)::int as "pct_ident", sum(H.pstop-H.pstart+1) as "aln_length",H.genasm_id,H.chr,H.plus_strand,min(H.gstart) as "gstart", max(H.gstop) as "gstop" from p2gblathsp H join p2gblatalnhsp AH on H.p2gblathsp_id=AH.p2gblathsp_id join p2gblataln A on AH.p2gblataln_id=A.p2gblataln_id join pseq Q on H.pseq_id=Q.pseq_id group by AH.p2gblataln_id,H.pseq_id,H.genasm_id,H.chr,H.plus_strand,A.ident,Q.len; CREATE VIEW Time: 103.041 ms rkh@csb-dev=> select * from v1 where pseq_id=76 \g /dev/null Time: 31973.979 ms Okay, that's ~100x slower. The plans: rkh@csb-dev=> explain select distinct on QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Unique (cost=11157.75..11187.26 rows=454 width=40) -> GroupAggregate (cost=11157.75..11186.13 rows=454 width=40) -> Sort (cost=11157.75..11158.89 rows=454 width=40) Sort Key: ah.p2gblataln_id, h.pseq_id, h.genasm_id, h.chr, h.plus_strand, a.ident, q.len -> Nested Loop (cost=11125.62..11137.71 rows=454 width=40) -> Index Scan using pseq_pkey on pseq q (cost=0.00..3.01 rows=2 width=6) Index Cond: (76 = pseq_id) -> Materialize (cost=11125.62..11127.89 rows=227 width=38) -> Nested Loop (cost=546.15..11125.62 rows=227 width=38) -> Hash Join (cost=546.15..10438.72 rows=227 width=34) Hash Cond: ("outer".p2gblathsp_id = "inner".p2gblathsp_id) -> Seq Scan on p2gblatalnhsp ah (cost=0.00..6504.03 rows=451503 width=8) -> Hash (cost=545.58..545.58 rows=227 width=34) -> Index Scan using p2gblathsp_p_lookup on p2gblathsp h (cost=0.00..545.58 rows=227 wid Index Cond: (pseq_id = 76) -> Index Scan using p2gblataln_pkey on p2gblataln a (cost=0.00..3.01 rows=1 width=8) Index Cond: ("outer".p2gblataln_id = a.p2gblataln_id) (17 rows) rkh@csb-dev=> explain select * from v1 where pseq_id=76; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Subquery Scan v1 (cost=246907.54..281897.70 rows=2258 width=77) Filter: (pseq_id = 76) -> Unique (cost=246907.54..276254.13 rows=451486 width=40) -> GroupAggregate (cost=246907.54..275125.41 rows=451486 width=40) -> Sort (cost=246907.54..248036.25 rows=451486 width=40) Sort Key: ah.p2gblataln_id, h.pseq_id, h.genasm_id, h.chr, h.plus_strand, a.ident, q.len -> Hash Join (cost=14019.29..204503.24 rows=451486 width=40) Hash Cond: ("outer".p2gblataln_id = "inner".p2gblataln_id) -> Hash Join (cost=7632.79..191344.45 rows=451486 width=36) Hash Cond: ("outer".p2gblathsp_id = "inner".p2gblathsp_id) -> Merge Join (cost=0.00..176939.38 rows=451486 width=36) Merge Cond: ("outer".pseq_id = "inner".pseq_id) -> Index Scan using p2gblathsp_p_lookup on p2gblathsp h (cost=0.00..16102.40 rows=451485 widt -> Index Scan using pseq_pkey on pseq q (cost=0.00..159942.83 rows=1960257 width=6) -> Hash (cost=6504.03..6504.03 rows=451503 width=8) -> Seq Scan on p2gblatalnhsp ah (cost=0.00..6504.03 rows=451503 width=8) -> Hash (cost=5587.00..5587.00 rows=319800 width=8) -> Seq Scan on p2gblataln a (cost=0.00..5587.00 rows=319800 width=8) (18 rows) Obviously, the problem is that the pseq_id criterion needs to be pushed farther down into the view plan (as it was for the explicit query). Since I've typically had comparable performance between views and explicit queries, I'm curious to know what aspect of this query prevents better optimization. My understanding from the 7.4.1 HISTORY is that ANSI-style joins (with JOIN) are now optimized as efficiently as WHERE-style joins... am I wrong? Thanks for your time. Reece -- Reece Hart, http://www.in-machina.com/~reece/, GPG:0x25EC91A0 --=-nuh+BKthForExGDf52Jv Content-Type: text/html; charset=utf-8 I have a large query which I would like to place in a view. The explicit query is sufficiently fast, but the same query as a view is much slower and uses a different plan. I would appreciate an explanation of why this is, and, more importantly whether/how I might coax the view to use a different plan.


The original query:
rkh@csb-dev=> select distinct on (AH.p2gblataln_id) AH.p2gblataln_id,H.pseq_id,min(H.pstart) as "pstart",
max(H.pstop) as "pstop",A.ident,(A.ident/Q.len::float*100)::int as "pct_ident",
sum(H.pstop-H.pstart+1) as "aln_length",H.genasm_id,H.chr,H.plus_strand,min(H.gstart) as "gstart",
max(H.gstop) as "gstop"
from p2gblathsp H
join p2gblatalnhsp AH on H.p2gblathsp_id=AH.p2gblathsp_id
join p2gblataln A on AH.p2gblataln_id=A.p2gblataln_id
join pseq Q on H.pseq_id=Q.pseq_id
where H.pseq_id=76
group by AH.p2gblataln_id,H.pseq_id,H.genasm_id,H.chr,H.plus_strand,A.ident,Q.len
\g /dev/null
Time: 277.804 ms
Now as a view:
rkh@csb-dev=> create view v1 as
select distinct on (AH.p2gblataln_id) AH.p2gblataln_id,H.pseq_id,min(H.pstart) as "pstart",
max(H.pstop) as "pstop",A.ident,(A.ident/Q.len::float*100)::int as "pct_ident",
sum(H.pstop-H.pstart+1) as "aln_length",H.genasm_id,H.chr,H.plus_strand,min(H.gstart) as "gstart",
max(H.gstop) as "gstop"
from p2gblathsp H
join p2gblatalnhsp AH on H.p2gblathsp_id=AH.p2gblathsp_id
join p2gblataln A on AH.p2gblataln_id=A.p2gblataln_id
join pseq Q on H.pseq_id=Q.pseq_id
group by AH.p2gblataln_id,H.pseq_id,H.genasm_id,H.chr,H.plus_strand,A.ident,Q.len;
CREATE VIEW
Time: 103.041 ms

rkh@csb-dev=> select * from v1 where pseq_id=76 \g /dev/null
Time: 31973.979 ms

Okay, that's ~100x slower. The plans:

rkh@csb-dev=> explain select distinct on <snip... same as the first query above>
                                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------- Unique  (cost=11157.75..11187.26 rows=454 width=40)
   ->  GroupAggregate  (cost=11157.75..11186.13 rows=454 width=40)
         ->  Sort  (cost=11157.75..11158.89 rows=454 width=40)
               Sort Key: ah.p2gblataln_id, h.pseq_id, h.genasm_id, h.chr, h.plus_strand, a.ident, q.len
               ->  Nested Loop  (cost=11125.62..11137.71 rows=454 width=40)
                     ->  Index Scan using pseq_pkey on pseq q  (cost=0.00..3.01 rows=2 width=6)
                           Index Cond: (76 = pseq_id)
                     ->  Materialize  (cost=11125.62..11127.89 rows=227 width=38)
                           ->  Nested Loop  (cost=546.15..11125.62 rows=227 width=38)
                                 ->  Hash Join  (cost=546.15..10438.72 rows=227 width=34)
                                       Hash Cond: ("outer".p2gblathsp_id = "inner".p2gblathsp_id)
                                       ->  Seq Scan on p2gblatalnhsp ah  (cost=0.00..6504.03 rows=451503 width=8)
                                       ->  Hash  (cost=545.58..545.58 rows=227 width=34)
                                             ->  Index Scan using p2gblathsp_p_lookup on p2gblathsp h  (cost=0.00..545.58 rows=227 wid                                                   Index Cond: (pseq_id = 76)
                                 ->  Index Scan using p2gblataln_pkey on p2gblataln a  (cost=0.00..3.01 rows=1 width=8)
                                       Index Cond: ("outer".p2gblataln_id = a.p2gblataln_id)
(17 rows)
 


rkh@csb-dev=> explain select * from v1 where pseq_id=76;
                                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------- Subquery Scan v1  (cost=246907.54..281897.70 rows=2258 width=77)
   Filter: (pseq_id = 76)
   ->  Unique  (cost=246907.54..276254.13 rows=451486 width=40)
         ->  GroupAggregate  (cost=246907.54..275125.41 rows=451486 width=40)
               ->  Sort  (cost=246907.54..248036.25 rows=451486 width=40)
                     Sort Key: ah.p2gblataln_id, h.pseq_id, h.genasm_id, h.chr, h.plus_strand, a.ident, q.len
                     ->  Hash Join  (cost=14019.29..204503.24 rows=451486 width=40)
                           Hash Cond: ("outer".p2gblataln_id = "inner".p2gblataln_id)
                           ->  Hash Join  (cost=7632.79..191344.45 rows=451486 width=36)
                                 Hash Cond: ("outer".p2gblathsp_id = "inner".p2gblathsp_id)
                                 ->  Merge Join  (cost=0.00..176939.38 rows=451486 width=36)
                                       Merge Cond: ("outer".pseq_id = "inner".pseq_id)
                                       ->  Index Scan using p2gblathsp_p_lookup on p2gblathsp h  (cost=0.00..16102.40 rows=451485 widt        
--=-nuh+BKthForExGDf52Jv--