pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Merlin Moncure <mmoncure@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: join to view over custom aggregate seems like it should be faster
Date: Tue, 10 Apr 2007 08:59:23 -0400
Message-ID: <b42b73150704100559n3ccb9d89p1767a0fa94324375@mail.gmail.com> (raw)
In-Reply-To: <b42b73150704091436g7fd1f903q1209218fc5eee88d@mail.gmail.com>
References: <b42b73150704091205o668a084y1693520c974f3c6b@mail.gmail.com>
	<10651.1176152866@sss.pgh.pa.us>
	<b42b73150704091436g7fd1f903q1209218fc5eee88d@mail.gmail.com>

On 4/9/07, Merlin Moncure <mmoncure@gmail.com> wrote:
> On 4/9/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > "Merlin Moncure" <mmoncure@gmail.com> writes:
> > > I have an odd performance issue on 8.2 that I'd thought I'd document
> > > here.  I have a workaround, but I'm if there is something that I'm not
> > > seeing.
> >
> > It's hard to comment on this without seeing the full details of the view
> > and tables.  I'm wondering where the SubPlans are coming from, for instance.
>
> ah, it looks like the aggregate is being re-expanded for each field
> returned by the aggregate.  I notice this for non-trivial record
> returning functions also.  standard m.o. is to push into a subquery
> and expand afterwords.

[sorry for the deluge of info]

I cleaned up the view from:
create or replace view latest_download as
 select software_binary_id, host_id,
   ((
     select latest_software_download(
           (bds_status_id,
           mtime,
           dl_window_open,
           dl_window_close,
           download_start,
           download_stop,
           info,
           userupgradeable,
           overrideflag,
           percent_complete)::software_download_data)
   )::software_download_data).*
   from software_download group by host_id, software_binary_id;

to this:
create or replace view latest_download as
  select software_binary_id, host_id, (v).* from
    (
      select
        software_binary_id, host_id,
        latest_software_download(
        (bds_status_id,
        mtime,
        dl_window_open,
        dl_window_close,
        download_start,
        download_stop,
        info,
        userupgradeable,
        overrideflag,
        percent_complete)::software_download_data) as v
        from software_download group by host_id, software_binary_id
    ) q;

this cleaned up the odd subplans but is still slow:
dev20400=# explain analyze select * from foo join latest_download
using (host_id, software_binary_id);

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=1308.84..1467.81 rows=25 width=40) (actual
time=1472.668..1914.799 rows=494 loops=1)
   Hash Cond: ((q.host_id = foo.host_id) AND (q.software_binary_id =
foo.software_binary_id))
   ->  HashAggregate  (cost=1293.48..1350.17 rows=4535 width=94)
(actual time=1467.002..1700.388 rows=37247 loops=1)
         ->  Seq Scan on software_download  (cost=0.00..953.42
rows=45342 width=94) (actual time=0.014..274.747 rows=45342 loops=1)
   ->  Hash  (cost=7.94..7.94 rows=494 width=8) (actual
time=5.028..5.028 rows=494 loops=1)
         ->  Seq Scan on foo  (cost=0.00..7.94 rows=494 width=8)
(actual time=0.022..2.507 rows=494 loops=1)
 Total runtime: 1918.721 ms

compare it to this:
dev20400=# explain analyze select * from foo f where exists (select *
from latest_download where host_id = f.host_id and software_binary_id
= f.software_binary_id);

    QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on foo f  (cost=0.00..3122.01 rows=247 width=8) (actual
time=0.152..45.941 rows=494 loops=1)
   Filter: (subplan)
   SubPlan
     ->  Subquery Scan q  (cost=0.00..6.30 rows=1 width=40) (actual
time=0.081..0.081 rows=1 loops=494)
           ->  GroupAggregate  (cost=0.00..6.29 rows=1 width=94)
(actual time=0.065..0.065 rows=1 loops=494)
                 ->  Index Scan using software_download_idx on
software_download  (cost=0.00..6.27 rows=1 width=94) (actual
time=0.013..0.021 r
                       Index Cond: ((host_id = $0) AND
(software_binary_id = $1))
 Total runtime: 48.323 ms
(8 rows)

Time: 49.851 ms

I since I need both sides, I can't figure out a way to force the index
to be used during the join except to use a function to look up the
view based on the key, which works:
dev20400=# explain analyze select latest_download(host_id,
software_binary_id) from foo;
                                                         QUERY PLAN
--------------------------------------------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..9.18 rows=494 width=8) (actual
time=0.566..51.605 rows=494 loops=1)
 Total runtime: 54.290 ms
(2 rows)

dev20400=# explain analyze select * from latest_download where host_id
= 1 and software_binary_id = 12345;
                                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 Subquery Scan q  (cost=0.00..6.30 rows=1 width=40) (actual
time=0.046..0.046 rows=0 loops=1)
   ->  GroupAggregate  (cost=0.00..6.29 rows=1 width=94) (actual
time=0.035..0.035 rows=0 loops=1)
         ->  Index Scan using software_download_idx on
software_download  (cost=0.00..6.27 rows=1 width=94) (actual
time=0.024..0.024 rows=0 lo
               Index Cond: ((host_id = 1) AND (software_binary_id = 12345))
 Total runtime: 0.134 ms

For some reason, I can't get the index to be used on the table sitting
under a view during a join, even though it should be, or at least it
seems....

merlin



view thread (8+ messages)  latest in thread

Message-ID: <b42b73150704100559n3ccb9d89p1767a0fa94324375@mail.gmail.com>
Permalink:  ../b42b73150704100559n3ccb9d89p1767a0fa94324375@mail.gmail.com/
Also on:    postgresql.org/message-id/b42b73150704100559n3ccb9d89p1767a0fa94324375@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: mmoncure@gmail.com, tgl@sss.pgh.pa.us
  Subject: Re: join to view over custom aggregate seems like it should be faster
  In-Reply-To: <b42b73150704100559n3ccb9d89p1767a0fa94324375@mail.gmail.com>
  References: <b42b73150704091205o668a084y1693520c974f3c6b@mail.gmail.com> <10651.1176152866@sss.pgh.pa.us> <b42b73150704091436g7fd1f903q1209218fc5eee88d@mail.gmail.com> <b42b73150704100559n3ccb9d89p1767a0fa94324375@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox