Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 193086327BF for ; Mon, 26 Jul 2010 05:35:56 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 52585-10 for ; Mon, 26 Jul 2010 08:35:47 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-fx0-f46.google.com (mail-fx0-f46.google.com [209.85.161.46]) by mail.postgresql.org (Postfix) with ESMTP id 211BC62DBEB for ; Mon, 26 Jul 2010 05:35:44 -0300 (ADT) Received: by fxm14 with SMTP id 14so5944631fxm.19 for ; Mon, 26 Jul 2010 01:35:43 -0700 (PDT) MIME-Version: 1.0 Received: by 10.223.119.10 with SMTP id x10mr5981824faq.1.1280133343451; Mon, 26 Jul 2010 01:35:43 -0700 (PDT) Received: by 10.223.16.152 with HTTP; Mon, 26 Jul 2010 01:35:43 -0700 (PDT) Date: Mon, 26 Jul 2010 10:35:43 +0200 Message-ID: Subject: Big difference in time returned by EXPLAIN ANALYZE SELECT ... AND SELECT ... From: =?UTF-8?Q?Piotr_Gasid=C5=82o?= To: pgsql-performance@postgresql.org Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.5 tagged_above=-10 required=5 tests=BAYES_05=-0.5 X-Spam-Level: X-Archive-Number: 201007/369 X-Sequence-Number: 39758 Hello, I've found strange problem in my database (8.4.4, but also 9.0beta3, default postgresql.conf, shared_buffers raised to 256MB). EXPLAIN ANALYZE SELECT ... Total runtime: 4.782 ms Time: 25,970 ms SELECT ... ... (21 rows) Time: 23,042 ms Test done in psql connected by socket to server (same host, using \timing to get runtime). Does big difference in "Total runtime" and "Time" is normal? I've notice, that removing one index (not used in query,ocache_*_ukey, used by me only to have data integrity) gives me: EXPLAIN ANALYZE SELECT ... Total runtime: 3.439 ms Time: 13,324 ms Why such big difference in timing (vs. query with this index)? Query is: select oc_h_id,oc_duration,SUM(oc_count) FROM ocache WHERE oc_date_from >=3D '2010-07-22'::date AND oc_date_from >=3D '2010-07-24'::date AND oc_h_id =3D ANY('{"32842","3095","27929","2229","22769","3098","33433","22559","226","2= 130","226","2130","2229","3095","3098","22559","22769","27929","32842","334= 33"}'::int[]) GROUP BY oc_h_id, oc_duration; EXPLAIN ANALYZE is: HashAggregate (cost=3D42060.58..42095.53 rows=3D2796 width=3D12) (actual time=3D4.357..4.368 rows=3D21 loops=3D1) -> Append (cost=3D0.00..41850.91 rows=3D27955 width=3D12) (actual time=3D0.432..3.928 rows=3D1439 loops=3D1) -> Seq Scan on ocache (cost=3D0.00..17.20 rows=3D6 width=3D12) (actual time=3D0.002..0.002 rows=3D0 loops=3D1) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date) AND (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[]))) -> Bitmap Heap Scan on ocache_2010_7 ocache (cost=3D357.41..8117.29 rows=3D5466 width=3D12) (actual time=3D0.430..0.582 rows=3D196 loops=3D1) Recheck Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date)) -> Bitmap Index Scan on ocache_2010_7_oc_h_id_key (cost=3D0.00..356.04 rows=3D16397 width=3D0) (actual time=3D0.174..0.174 rows=3D1156 loops=3D1) Index Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) -> Bitmap Heap Scan on ocache_2010_8 ocache (cost=3D370.91..9067.15 rows=3D6060 width=3D12) (actual time=3D0.175..0.615 rows=3D562 loops=3D1) Recheck Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date)) -> Bitmap Index Scan on ocache_2010_8_oc_h_id_key (cost=3D0.00..369.39 rows=3D18181 width=3D0) (actual time=3D0.156..0.156 rows=3D1124 loops=3D1) Index Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) -> Bitmap Heap Scan on ocache_2010_9 ocache (cost=3D389.47..9891.79 rows=3D6703 width=3D12) (actual time=3D0.158..0.513 rows=3D448 loops=3D1) Recheck Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date)) -> Bitmap Index Scan on ocache_2010_9_oc_h_id_key (cost=3D0.00..387.80 rows=3D20108 width=3D0) (actual time=3D0.140..0.140 rows=3D896 loops=3D1) Index Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) -> Bitmap Heap Scan on ocache_2010_10 ocache (cost=3D268.42..6735.90 rows=3D4721 width=3D12) (actual time=3D0.107..0.300 rows=3D229 loops=3D1) Recheck Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date)) -> Bitmap Index Scan on ocache_2010_10_oc_h_id_key (cost=3D0.00..267.24 rows=3D14162 width=3D0) (actual time=3D0.096..0.096 rows=3D458 loops=3D1) Index Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) -> Bitmap Heap Scan on ocache_2010_11 ocache (cost=3D139.48..3340.84 rows=3D2395 width=3D12) (actual time=3D0.046..0.047 rows=3D4 loops=3D1) Recheck Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date)) -> Bitmap Index Scan on ocache_2010_11_oc_h_id_key (cost=3D0.00..138.88 rows=3D7184 width=3D0) (actual time=3D0.040..0.040 row= s=3D8 loops=3D1) Index Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) -> Bitmap Heap Scan on ocache_2010_12 ocache (cost=3D108.78..1766.50 rows=3D1223 width=3D12) (actual time=3D0.041..0.041 rows=3D0 loops=3D1) Recheck Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date)) -> Bitmap Index Scan on ocache_2010_12_oc_h_id_key (cost=3D0.00..108.48 rows=3D3668 width=3D0) (actual time=3D0.040..0.040 row= s=3D0 loops=3D1) Index Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) -> Bitmap Heap Scan on ocache_2011_1 ocache (cost=3D70.63..432.15 rows=3D246 width=3D12) (actual time=3D0.036..0.036 rows=3D0 loops=3D1) Recheck Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date)) -> Bitmap Index Scan on ocache_2011_1_oc_h_id_key (cost=3D0.00..70.57 rows=3D738 width=3D0) (actual time=3D0.035..0.035 rows= =3D0 loops=3D1) Index Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) -> Bitmap Heap Scan on ocache_2011_2 ocache (cost=3D65.72..368.20 rows=3D204 width=3D12) (actual time=3D0.038..0.038 rows=3D0 loops=3D1) Recheck Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date)) -> Bitmap Index Scan on ocache_2011_2_oc_h_id_key (cost=3D0.00..65.67 rows=3D612 width=3D0) (actual time=3D0.038..0.038 rows= =3D0 loops=3D1) Index Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) -> Bitmap Heap Scan on ocache_2011_3 ocache (cost=3D60.36..290.04 rows=3D147 width=3D12) (actual time=3D0.037..0.037 rows=3D0 loops=3D1) Recheck Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date)) -> Bitmap Index Scan on ocache_2011_3_oc_h_id_key (cost=3D0.00..60.32 rows=3D442 width=3D0) (actual time=3D0.036..0.036 rows= =3D0 loops=3D1) Index Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) -> Bitmap Heap Scan on ocache_2011_4 ocache (cost=3D59.75..243.87 rows=3D118 width=3D12) (actual time=3D0.034..0.034 rows=3D0 loops=3D1) Recheck Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date)) -> Bitmap Index Scan on ocache_2011_4_oc_h_id_key (cost=3D0.00..59.72 rows=3D353 width=3D0) (actual time=3D0.033..0.033 rows= =3D0 loops=3D1) Index Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) -> Bitmap Heap Scan on ocache_2011_5 ocache (cost=3D54.99..190.27 rows=3D86 width=3D12) (actual time=3D0.032..0.032 row= s=3D0 loops=3D1) Recheck Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date)) -> Bitmap Index Scan on ocache_2011_5_oc_h_id_key (cost=3D0.00..54.97 rows=3D257 width=3D0) (actual time=3D0.031..0.031 rows= =3D0 loops=3D1) Index Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) -> Bitmap Heap Scan on ocache_2011_6 ocache (cost=3D32.64..182.12 rows=3D80 width=3D12) (actual time=3D1.299..1.299 row= s=3D0 loops=3D1) Recheck Cond: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date)) Filter: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) -> Bitmap Index Scan on ocache_2011_6_ukey (cost=3D0.00..32.62 rows=3D837 width=3D0) (actual time=3D0.224..0.224 rows=3D2510 loops=3D1) Index Cond: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date)) -> Bitmap Heap Scan on ocache_2011_7 ocache (cost=3D55.15..195.99 rows=3D90 width=3D12) (actual time=3D0.033..0.033 row= s=3D0 loops=3D1) Recheck Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date)) -> Bitmap Index Scan on ocache_2011_7_oc_h_id_key (cost=3D0.00..55.12 rows=3D271 width=3D0) (actual time=3D0.033..0.033 rows= =3D0 loops=3D1) Index Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) -> Bitmap Heap Scan on ocache_2011_8 ocache (cost=3D54.99..192.43 rows=3D87 width=3D12) (actual time=3D0.033..0.033 row= s=3D0 loops=3D1) Recheck Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date)) -> Bitmap Index Scan on ocache_2011_8_oc_h_id_key (cost=3D0.00..54.97 rows=3D261 width=3D0) (actual time=3D0.033..0.033 rows= =3D0 loops=3D1) Index Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) -> Bitmap Heap Scan on ocache_2011_9 ocache (cost=3D54.99..188.23 rows=3D85 width=3D12) (actual time=3D0.033..0.033 row= s=3D0 loops=3D1) Recheck Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date)) -> Bitmap Index Scan on ocache_2011_9_oc_h_id_key (cost=3D0.00..54.97 rows=3D256 width=3D0) (actual time=3D0.032..0.032 rows= =3D0 loops=3D1) Index Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) -> Bitmap Heap Scan on ocache_2011_10 ocache (cost=3D54.84..183.72 rows=3D82 width=3D12) (actual time=3D0.032..0.032 row= s=3D0 loops=3D1) Recheck Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date)) -> Bitmap Index Scan on ocache_2011_10_oc_h_id_key (cost=3D0.00..54.82 rows=3D247 width=3D0) (actual time=3D0.032..0.032 rows= =3D0 loops=3D1) Index Cond: (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[])) -> Seq Scan on ocache_2011_11 ocache (cost=3D0.00..17.20 rows=3D6 width=3D12) (actual time=3D0.001..0.001 rows=3D0 loops=3D1) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date) AND (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[]))) -> Seq Scan on ocache_2011_12 ocache (cost=3D0.00..17.20 rows=3D6 width=3D12) (actual time=3D0.000..0.000 rows=3D0 loops=3D1) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date) AND (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[]))) -> Seq Scan on ocache_2012_1 ocache (cost=3D0.00..17.20 rows=3D6 width=3D12) (actual time=3D0.001..0.001 rows=3D0 loops=3D1) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date) AND (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[]))) -> Seq Scan on ocache_2012_2 ocache (cost=3D0.00..17.20 rows=3D6 width=3D12) (actual time=3D0.000..0.000 rows=3D0 loops=3D1) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date) AND (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[]))) -> Seq Scan on ocache_2012_3 ocache (cost=3D0.00..17.20 rows=3D6 width=3D12) (actual time=3D0.001..0.001 rows=3D0 loops=3D1) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date) AND (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[]))) -> Seq Scan on ocache_2012_4 ocache (cost=3D0.00..17.20 rows=3D6 width=3D12) (actual time=3D0.000..0.000 rows=3D0 loops=3D1) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date) AND (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[]))) -> Seq Scan on ocache_2012_5 ocache (cost=3D0.00..17.20 rows=3D6 width=3D12) (actual time=3D0.001..0.001 rows=3D0 loops=3D1) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date) AND (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[]))) -> Seq Scan on ocache_2012_6 ocache (cost=3D0.00..17.20 rows=3D6 width=3D12) (actual time=3D0.000..0.000 rows=3D0 loops=3D1) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date) AND (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[]))) -> Seq Scan on ocache_2012_7 ocache (cost=3D0.00..17.20 rows=3D6 width=3D12) (actual time=3D0.000..0.000 rows=3D0 loops=3D1) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date) AND (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[]))) -> Seq Scan on ocache_2012_8 ocache (cost=3D0.00..17.20 rows=3D6 width=3D12) (actual time=3D0.000..0.000 rows=3D0 loops=3D1) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date) AND (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[]))) -> Seq Scan on ocache_2012_9 ocache (cost=3D0.00..17.20 rows=3D6 width=3D12) (actual time=3D0.001..0.001 rows=3D0 loops=3D1) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date) AND (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[]))) -> Seq Scan on ocache_2012_10 ocache (cost=3D0.00..17.20 rows=3D6 width=3D12) (actual time=3D0.001..0.001 rows=3D0 loops=3D1) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date) AND (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[]))) -> Seq Scan on ocache_2012_11 ocache (cost=3D0.00..17.20 rows=3D6 width=3D12) (actual time=3D0.000..0.000 rows=3D0 loops=3D1) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date) AND (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[]))) -> Seq Scan on ocache_2012_12 ocache (cost=3D0.00..17.20 rows=3D6 width=3D12) (actual time=3D0.001..0.001 rows=3D0 loops=3D1) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date) AND (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[]))) -> Seq Scan on ocache_2013_1 ocache (cost=3D0.00..17.20 rows=3D6 width=3D12) (actual time=3D0.000..0.000 rows=3D0 loops=3D1) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date) AND (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[]))) -> Seq Scan on ocache_2013_2 ocache (cost=3D0.00..17.20 rows=3D6 width=3D12) (actual time=3D0.001..0.001 rows=3D0 loops=3D1) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date) AND (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[]))) -> Seq Scan on ocache_2013_3 ocache (cost=3D0.00..17.20 rows=3D6 width=3D12) (actual time=3D0.000..0.000 rows=3D0 loops=3D1) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date) AND (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[]))) -> Seq Scan on ocache_2013_4 ocache (cost=3D0.00..17.20 rows=3D6 width=3D12) (actual time=3D0.000..0.000 rows=3D0 loops=3D1) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date) AND (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[]))) -> Seq Scan on ocache_2013_5 ocache (cost=3D0.00..17.20 rows=3D6 width=3D12) (actual time=3D0.000..0.000 rows=3D0 loops=3D1) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date) AND (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[]))) -> Seq Scan on ocache_2013_6 ocache (cost=3D0.00..17.20 rows=3D6 width=3D12) (actual time=3D0.000..0.000 rows=3D0 loops=3D1) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date) AND (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[]))) -> Seq Scan on ocache_2013_7 ocache (cost=3D0.00..17.20 rows=3D6 width=3D12) (actual time=3D0.000..0.000 rows=3D0 loops=3D1) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date) AND (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[]))) -> Seq Scan on ocache_2013_8 ocache (cost=3D0.00..17.20 rows=3D6 width=3D12) (actual time=3D0.000..0.000 rows=3D0 loops=3D1) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date) AND (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[]))) -> Seq Scan on ocache_2013_9 ocache (cost=3D0.00..17.20 rows=3D6 width=3D12) (actual time=3D0.000..0.000 rows=3D0 loops=3D1) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date) AND (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[]))) -> Seq Scan on ocache_2013_10 ocache (cost=3D0.00..17.20 rows=3D6 width=3D12) (actual time=3D0.000..0.000 rows=3D0 loops=3D1) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date) AND (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[]))) -> Seq Scan on ocache_2013_11 ocache (cost=3D0.00..17.20 rows=3D6 width=3D12) (actual time=3D0.000..0.000 rows=3D0 loops=3D1) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date) AND (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[]))) -> Seq Scan on ocache_2013_12 ocache (cost=3D0.00..17.20 rows=3D6 width=3D12) (actual time=3D0.001..0.001 rows=3D0 loops=3D1) Filter: ((oc_date_from >=3D '2010-07-22'::date) AND (oc_date_from >=3D '2010-07-24'::date) AND (oc_h_id =3D ANY ('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095= ,3098,22559,22769,27929,32842,33433}'::integer[]))) Total runtime: 4.725 ms (137 rows) Paritions are rather small, some of them are empty: > select count(*) from ocache; count -------- 907688 (1 row) > \d ocache Table "public.ocache" Column | Type | Modifiers -----------------------+-----------------------------+----------- oc_count | integer | oc_to_id | integer | oc_hc_ids | integer[] | oc_h_id | integer | oc_hg_id | integer | oc_hg_category | numeric(2,1) | oc_r_id | integer | oc_date_from | date | oc_date_to | date | oc_duration | integer | oc_transport | integer | oc_price_min | numeric | oc_price_max | numeric | oc_rc_ids | integer[] | oc_orc_ids | integer[] | oc_fc_ids | integer[] | oc_ofc_id_1 | integer | oc_ap_ids_from1 | integer[] | oc_bc_ids_from | integer[] | oc_obc_ids_from | integer[] | oc_o_ids | integer[] | oc_price_avg | numeric | oc_o_date_updated_min | timestamp without time zone | oc_o_date_updated_max | timestamp without time zone | Number of child tables: 48 (Use \d+ to list them.) Data partitioned by month (oc_date_from column, tables created for years: 2010, 2011, 2012, 2013), example child table: > \d+ ocache_2010_12 Table "public.ocache_2010_12" Column | Type | Modifiers | Storage | Description -----------------------+-----------------------------+-----------+---------= -+------------- oc_count | integer | | plain = | oc_to_id | integer | | plain = | oc_hc_ids | integer[] | | extended= | oc_h_id | integer | | plain = | oc_hg_id | integer | | plain = | oc_hg_category | numeric(2,1) | | main = | oc_r_id | integer | | plain = | oc_date_from | date | | plain = | oc_date_to | date | | plain = | oc_duration | integer | | plain = | oc_transport | integer | | plain = | oc_price_min | numeric | | main = | oc_price_max | numeric | | main = | oc_rc_ids | integer[] | | extended= | oc_orc_ids | integer[] | | extended= | oc_fc_ids | integer[] | | extended= | oc_ofc_id_1 | integer | | plain = | oc_ap_ids_from1 | integer[] | | extended= | oc_bc_ids_from | integer[] | | extended= | oc_obc_ids_from | integer[] | | extended= | oc_o_ids | integer[] | | extended= | oc_price_avg | numeric | | main = | oc_o_date_updated_min | timestamp without time zone | | plain = | oc_o_date_updated_max | timestamp without time zone | | plain = | Indexes: "ocache_2010_12_oc_ap_ids_from1_key" gist (oc_ap_ids_from1) "ocache_2010_12_oc_h_id_key" btree (oc_h_id) "ocache_2010_12_oc_hg_id_key" btree (oc_hg_id) "ocache_2010_12_oc_obc_ids_from_key" gist (oc_obc_ids_from) "ocache_2010_12_oc_r_id_key" btree (oc_r_id) "ocache_2010_12_oc_to_id_key" btree (oc_to_id) Check constraints: "ocache_2010_12_oc_date_from_check" CHECK (oc_date_from >=3D '2010-12-01'::date AND oc_date_from <=3D '2011-01-01'::date) Inherits: ocache Has OIDs: no Options: fillfactor=3D80 --=20 Piotr Gasid=C5=82o