Tom Lane wrote:
Josh Berkus <> writes:

The issue here is that the planner is capable of "pushing down" the WHERE criteria into the first view, but not into the second, "nested" view, and so postgres materializes the UNIONed data set before perfoming the join.

Thing is, I seem to recall that this particular issue was something Tom fixed
a while ago.  Which is why I wanted to know what version Gaetano is using.

It's still true that we can't generate a nestloop-with-inner-indexscan
join plan if the inner side is anything more complex than a single table
scan.  Since that's the only plan that gives you any chance of not
scanning the whole partitioned table, it's rather a hindrance :-(

It might be possible to fix this by treating the nestloop's join
conditions as "push down-able" criteria, instead of the present rather
ad hoc method for generating nestloop/indexscan plans.  It'd be quite
a deal of work though, and I'm concerned about how slow the planner
might run if we did do it like that.

I don't know if this will help my attempt to perform an horizontal partition, if it do I think that it can solve lot of problems out there, I tried the inheritance technique too:

The table user_logs is the original one, I created two tables extending this 

CREATE TABLE user_logs_2003_h () inherits (user_logs);
CREATE TABLE user_logs_2002_h () inherits (user_logs);

I defined on this table the index already defined on user_logs.

And this is the result:

empdb=# explain analyze select * from user_logs where id_user = 
 Result  (cost=0.00..426.33 rows=335 width=67) (actual time=20.891..129.218 
rows=98 loops=1)
   ->  Append  (cost=0.00..426.33 rows=335 width=67) (actual 
time=20.871..128.643 rows=98 loops=1)
         ->  Index Scan using idx_user_user_logs on user_logs  
(cost=0.00..133.11 rows=66 width=67) (actual time=20.864..44.594 rows=3 loops=1)
               Index Cond: (id_user = 4185)
         ->  Index Scan using idx_user_user_logs_2003_h on user_logs_2003_h 
user_logs  (cost=0.00..204.39 rows=189 width=67) (actual time=1.507..83.662 
rows=95 loops=1)
               Index Cond: (id_user = 4185)
         ->  Index Scan using idx_user_user_logs_2002_h on user_logs_2002_h 
user_logs  (cost=0.00..88.83 rows=80 width=67) (actual time=0.206..0.206 rows=0 
               Index Cond: (id_user = 4185)
 Total runtime: 129.500 ms
(9 rows)

that is good, but now look what happen in a view like this one:

create view to_delete AS SELECT v.login, u.* from user_login v, user_logs u where v.id_user = u.id_user;

empdb=# explain analyze select * from to_delete where login = 'kalman';
                                                                   QUERY PLAN
 Hash Join  (cost=4.01..65421.05 rows=143 width=79) (actual 
time=1479.738..37121.511 rows=98 loops=1)
   Hash Cond: ("outer".id_user = "inner".id_user)
   ->  Append  (cost=0.00..50793.17 rows=2924633 width=67) (actual 
time=21.391..33987.363 rows=2927428 loops=1)
         ->  Seq Scan on user_logs u  (cost=0.00..7195.22 rows=411244 width=67) 
(actual time=21.385..5641.307 rows=414039 loops=1)
         ->  Seq Scan on user_logs_2003_h u  (cost=0.00..34833.95 rows=2008190 
width=67) (actual time=0.024..18031.218 rows=2008190 loops=1)
         ->  Seq Scan on user_logs_2002_h u  (cost=0.00..8764.00 rows=505199 
width=67) (actual time=0.005..5733.554 rows=505199 loops=1)
   ->  Hash  (cost=4.00..4.00 rows=2 width=16) (actual time=0.195..0.195 rows=0 
         ->  Index Scan using user_login_login_key on user_login v  
(cost=0.00..4.00 rows=2 width=16) (actual time=0.155..0.161 rows=1 loops=1)
               Index Cond: ((login)::text = 'kalman'::text)
 Total runtime: 37122.069 ms
(10 rows)

and how you can see this path is not applicable too :-(

Any other suggestion ?

Gaetano Mendola

---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster

Reply via email to