Gaetano Mendola wrote:
Hash: SHA1

Richard Huxton wrote:

Gaetano Mendola wrote:

running a 7.4.5 engine, I'm facing this bad plan:

empdb=# explain analyze SELECT

empdb-#                    FROM v_sc_user_request
empdb-#                    WHERE
empdb-#                         login = 'babinow1'
empdb-#                    LIMIT 10 ;

                    ->  Subquery Scan vsp  (cost=985.73..1016.53
rows=1103 width=12) (actual time=25.328..1668.754 rows=493 loops=31)
                          ->  Merge Join  (cost=985.73..1011.01
rows=1103 width=130) (actual time=25.321..1666.666 rows=493 loops=31)
                                Merge Cond: ("outer".id_program =

The problem to address is in this subquery. That's a total of 31 x
(1668.754 - 25.328) = 50seconds (about).

Since your query is so simple, I'm guessing v_sc_user_request is a view.
Can you provide the definition?

Of course:

CREATE OR REPLACE VIEW v_sc_user_request AS
      v_sat_request vsr LEFT OUTER JOIN v_sc_packages vsp USING ( id_package )
      vsr.request_time > now() - '1 month'::interval AND
      vsr.expired = FALSE
  ORDER BY id_sat_request DESC

CREATE OR REPLACE VIEW v_sc_packages AS SELECT * FROM v_programs vpr, v_packages vpk, v_sequences vs

     ------------ JOIN -------------
      vpr.id_program = vs.id_program AND
      vpk.id_package = vs.id_package AND
      vs.estimated_start IS NOT NULL

     sat_request sr,
     url         u,
     user_login  ul
     ---------------- JOIN ---------------------
     sr.id_url  = u.id_url AND
     sr.id_user = ul.id_user

OK, so looking at the original EXPLAIN the order of processing seems to be:
1. v_sat_request is evaluated and filtered on login='...' (lines 7..15)
This gives us 31 rows
2. The left-join from v_sat_request to v_sc_packages is processed (lines 5..6)
This involves the subquery scan on vsp (from line 16) where it seems to think the best idea is a merge join of programs to sequences.

So - I think we need to look at the performance of your view "v_sc_packages" and the views that it depends on. OK - can you reply to this with just the definitions of v_sc_packages and what it depends on, and we can have a look at that.

Do you need all these tables involved in this query? I don't think PG is smart enough to completely discard a join if it's not needed by the output. Thinking about it, I'm not sure you could safely.
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
     joining column's datatypes do not match

Reply via email to