X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 768498B9B53 for ; Wed, 2 Feb 2005 00:16:29 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 29167-06 for ; Wed, 2 Feb 2005 00:16:19 +0000 (GMT) Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.250.190.2]) by svr1.postgresql.org (Postfix) with ESMTP id D35F38B9B49 for ; Wed, 2 Feb 2005 00:16:18 +0000 (GMT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id CE84531DAA; Wed, 2 Feb 2005 01:16:17 +0100 (MET) From: Gaetano Mendola X-Newsgroups: pgsql.performance Subject: horizontal partition Date: Wed, 02 Feb 2005 01:16:17 +0100 Organization: PYRENET Midi-pyrenees Provider Lines: 113 Message-ID: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: abuse@pyrenet.fr User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.009 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200502/25 X-Sequence-Number: 10354 Hi all, I have a big table with ~ 10 Milion rows, and is a very pain administer it, so after years I convinced my self to partition it and replace the table usage ( only for reading ) with a view. Now my user_logs table is splitted in 4: user_logs user_logs_2002 user_logs_2003 user_logs_2004 and the view v_user_logs is builded on top of these tables: CREATE OR REPLACE VIEW v_user_logs AS SELECT * FROM user_logs UNION ALL SELECT * FROM user_logs_2002 UNION ALL SELECT * FROM user_logs_2003 UNION ALL SELECT * FROM user_logs_2004 ; the view is performing really well: empdb=# explain analyze select * from v_user_logs where id_user = sp_id_user('kalman'); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------- Subquery Scan v_user_logs (cost=0.00..895.45 rows=645 width=88) (actual time=17.039..2345.388 rows=175 loops=1) -> Append (cost=0.00..892.23 rows=645 width=67) (actual time=17.030..2344.195 rows=175 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..120.70 rows=60 width=67) (actual time=17.028..17.036 rows=1 loops=1) -> Index Scan using idx_user_user_logs on user_logs (cost=0.00..120.40 rows=60 width=67) (actual time=17.012..17.018 rows=1 loops=1) Index Cond: (id_user = 4185) -> Subquery Scan "*SELECT* 2" (cost=0.00..475.44 rows=316 width=67) (actual time=49.406..1220.400 rows=79 loops=1) -> Index Scan using idx_user_user_logs_2004 on user_logs_2004 (cost=0.00..473.86 rows=316 width=67) (actual time=49.388..1219.386 rows=79 loops=1) Index Cond: (id_user = 4185) -> Subquery Scan "*SELECT* 3" (cost=0.00..204.33 rows=188 width=67) (actual time=59.375..1068.806 rows=95 loops=1) -> Index Scan using idx_user_user_logs_2003 on user_logs_2003 (cost=0.00..203.39 rows=188 width=67) (actual time=59.356..1067.934 rows=95 loops=1) Index Cond: (id_user = 4185) -> Subquery Scan "*SELECT* 4" (cost=0.00..91.75 rows=81 width=67) (actual time=37.623..37.623 rows=0 loops=1) -> Index Scan using idx_user_user_logs_2002 on user_logs_2002 (cost=0.00..91.35 rows=81 width=67) (actual time=37.618..37.618 rows=0 loops=1) Index Cond: (id_user = 4185) Total runtime: 2345.917 ms (15 rows) the problem is now if this view is used in others views like this: CREATE OR REPLACE VIEW v_ua_user_login_logout_tmp AS SELECT u.login, ul.* FROM user_login u, v_user_logs ul WHERE u.id_user = ul.id_user ; empdb=# explain analyze select * from v_ua_user_login_logout_tmp where login = 'kalman'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------- Hash Join (cost=4.01..228669.81 rows=173 width=100) (actual time=1544.784..116490.363 rows=175 loops=1) Hash Cond: ("outer".id_user = "inner".id_user) -> Subquery Scan ul (cost=0.00..193326.71 rows=7067647 width=88) (actual time=5.677..108190.096 rows=7067831 loops=1) -> Append (cost=0.00..157988.47 rows=7067647 width=67) (actual time=5.669..77109.995 rows=7067831 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..8158.48 rows=362548 width=67) (actual time=5.666..3379.178 rows=362862 loops=1) -> Seq Scan on user_logs (cost=0.00..6345.74 rows=362548 width=67) (actual time=5.645..1395.673 rows=362862 loops=1) -> Subquery Scan "*SELECT* 2" (cost=0.00..93663.88 rows=4191588 width=67) (actual time=9.149..35094.798 rows=4191580 loops=1) -> Seq Scan on user_logs_2004 (cost=0.00..72705.94 rows=4191588 width=67) (actual time=9.117..16531.486 rows=4191580 loops=1) -> Subquery Scan "*SELECT* 3" (cost=0.00..44875.33 rows=2008233 width=67) (actual time=0.562..24017.680 rows=2008190 loops=1) -> Seq Scan on user_logs_2003 (cost=0.00..34834.17 rows=2008233 width=67) (actual time=0.542..13224.265 rows=2008190 loops=1) -> Subquery Scan "*SELECT* 4" (cost=0.00..11290.78 rows=505278 width=67) (actual time=7.100..3636.163 rows=505199 loops=1) -> Seq Scan on user_logs_2002 (cost=0.00..8764.39 rows=505278 width=67) (actual time=6.446..1474.709 rows=505199 loops=1) -> Hash (cost=4.00..4.00 rows=1 width=16) (actual time=0.083..0.083 rows=0 loops=1) -> Index Scan using user_login_login_key on user_login u (cost=0.00..4.00 rows=1 width=16) (actual time=0.064..0.066 rows=1 loops=1) Index Cond: ((login)::text = 'kalman'::text) Total runtime: 116491.056 ms (16 rows) as you can see the index scan is not used anymore. Do you see any problem on this approach ? Regards Gaetano Mendola