Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ghDOT-0006HC-1Y for pgsql-performance@arkaria.postgresql.org; Wed, 09 Jan 2019 12:55:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ghDOR-0008AQ-BU for pgsql-performance@arkaria.postgresql.org; Wed, 09 Jan 2019 12:55:35 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ghDOQ-0008A9-Vs for pgsql-performance@lists.postgresql.org; Wed, 09 Jan 2019 12:55:35 +0000 Received: from smtp2.iter.org ([193.51.56.8]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ghDOI-0007rY-P5 for pgsql-performance@lists.postgresql.org; Wed, 09 Jan 2019 12:55:33 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=iter.org; l=22446; q=dns/txt; s=20170425; t=1547038527; x=1547643327; h=from:to:cc:subject:date:message-id:references: in-reply-to:content-transfer-encoding:mime-version; bh=9lO8pyRu3dbpdH1ciZ036tHl+Xfz9s+ByXatpUrn8Ac=; b=dGYLsR5MITniKsLLSSC8dOOwxG/F9wGT4vKGHQhCRgTK4YHujfc4mAPr 2DyAVA/+5/7JMbuBaa/nxs65deOLDytnS1e6uGmXODk1onO31I+K2+JWg pugciG+He7Ik8YMNEey6aUogaJGonuGzpGEO1wwZl4egzuOXdCP+24qj6 8=; Received: from XCH-CLU1N3.iter.org (10.10.242.164) by XCH-CLU1N2.iter.org (10.10.241.164) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256) id 15.1.1261.35; Wed, 9 Jan 2019 13:55:24 +0100 Received: from XCH-CLU1N3.iter.org ([fe80::f903:4e8a:c365:4c74]) by XCH-CLU1N3.iter.org ([fe80::f903:4e8a:c365:4c74%16]) with mapi id 15.01.1261.036; Wed, 9 Jan 2019 13:55:24 +0100 From: Abadie Lana To: Justin Pryzby CC: David Rowley , "pgsql-performance@lists.postgresql.org" Subject: RE: select query does not pick up the right index Thread-Topic: select query does not pick up the right index Thread-Index: AdSitwt5/9TyB7hoTX++E94tUlkiMgAOgIkAABh1BzAAAkhrgAACgegQ///wggD//+6z4IAAwWGA//9iMjD//rrHQAB4UUyA//wibdD/9yq/AP/shstA Date: Wed, 9 Jan 2019 12:55:24 +0000 Message-ID: <2ff1004f5faa4c9a841cb29c90473251@iter.org> References: <49406fb0c239480980ffc6c2a250d4e9@iter.org> <438dcc57f91d4a9fab12ae4078592d69@iter.org> <20190103234743.GU25379@telsasoft.com> <7e1a311617e441d2a40dc97fa9766753@iter.org> <399cd3f79a704b21a47e4305cebfa727@iter.org> <20190105042357.GK25379@telsasoft.com> <87705cc87ff74d6d9089adf227c5a9fb@iter.org> <20190108081528.GF25379@telsasoft.com> In-Reply-To: <20190108081528.GF25379@telsasoft.com> Accept-Language: en-GB, en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.14.32.57] Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk -----Original Message----- From: Justin Pryzby =20 Sent: 08 January 2019 09:15 To: Abadie Lana Cc: David Rowley ; pgsql-performance@lists.po= stgresql.org Subject: Re: select query does not pick up the right index On Mon, Jan 07, 2019 at 04:09:50PM +0000, Abadie Lana wrote: > "channel_pkey" PRIMARY KEY, btree (channel_id) > "unique_chname" UNIQUE CONSTRAINT, btree (name) > "channel_name_channel_id_idx" btree (name, channel_id) Note, the third index is more or less redundant. > I would say that when you have a partitioned table, running analyse on th= e parent table (which includes the children) does not give the same result = as running analyse on each individual child table. I don't know if it is an= expected behaviour? Right, for relkind=3D'r' inheritence, ANALYZE parent gathers 1) stats for t= he parent ONLY (stored with pg_stats inherited=3D'f'); and, 2) stats for th= e parent and its children (stored in pg_stats with inherited=3D't'). It *doesn't* update statistics for each of the children themselves. Note h= owever that for partitions of relkind=3D'p' tables (available since postgre= s 10) ANALYZE parent *ALSO* updates stats for the children. > But not the other query...still time-consuming because still using the=20 > wrong index in case of sample_buil_year (but curiously not the BRIN=20 > index) explain (analyze, buffers) select 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW= ',c.smpl_time,c.nanosecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array= _val from sample c WHERE c.channel_id =3D (SELECT channel_id FROM channel W= HERE name=3D'BUIL-B36-VA-RT-RT1:CL0001-2-ABW') order by c.smpl_time desc li= mit 5; Limit (cost=3D13.40..30.54 rows=3D5 width=3D112) (actual time=3D63= 411.725..63411.744 rows=3D3 loops=3D1) > Buffers: shared hit=3D38 read=3D193865 > InitPlan 1 (returns $0) > -> Index Scan using unique_chname on channel (cost=3D0.41..8.43 ro= ws=3D1 width=3D8) (actual time=3D0.039..0.040 rows=3D1 loops =3D1) > Index Cond: ((name)::text =3D 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW= '::text) > Buffers: shared hit=3D4 > -> Result (cost=3D4.96..5294364.58 rows=3D1544048 width=3D112) (actu= al time=3D63411.723..63411.740 rows=3D3 loops=3D1) > Buffers: shared hit=3D38 read=3D193865 > -> Merge Append (cost=3D4.96..5278924.10 rows=3D1544048 width= =3D80) (actual time=3D63411.719..63411.735 rows=3D3 loops=3D1) > Sort Key: c.smpl_time DESC > Buffers: shared hit=3D38 read=3D193865 > -> Index Scan Backward using sample_time_all_idx on sampl= e c (cost=3D0.12..8.14 rows=3D1 width=3D326) (actual time=3D0.048..0.048 r= ows=3D0 loops=3D1) > Index Cond: (channel_id =3D $0) > Buffers: shared hit=3D5 > -> Index Scan Backward using sample_time_b_idx on sample_= buil c_1 (cost=3D0.42..7775.26 rows=3D2096 width=3D320) (actual time=3D0.0= 08..0.009 rows=3D0 loops=3D1) > Index Cond: (channel_id =3D $0) > Buffers: shared hit=3D3 > -> Index Scan Backward using sample_time_c_idx on sample_= ctrl c_2 (cost=3D0.42..77785.57 rows=3D22441 width=3D320) (actual time=3D0= .006..0.006 rows=3D0 loops=3D1) > Index Cond: (channel_id =3D $0) > Buffers: shared hit=3D3 > -> Index Scan Backward using sample_time_u_idx on sample_= util c_3 (cost=3D0.43..14922.72 rows=3D3830 width=3D320) (actual time=3D0.= 008..0.008 rows=3D0 loops=3D1) > Index Cond: (channel_id =3D $0) > Buffers: shared hit=3D3 > -> Index Scan Backward using sample_time_bm_idx on sample= _buil_month c_4 (cost=3D0.56..2967.10 rows=3D740 width=3D74) (actual time= =3D0.011..0.025 rows=3D3 loops=3D1) > Index Cond: (channel_id =3D $0) > Buffers: shared hit=3D8 > -> Index Scan Backward using sample_time_yb1_idx on sampl= e_buil_year c_5 (cost=3D0.56..2186210.68 rows=3D665761 width=3D75) (actual= time=3D63411.573..63411.574 rows=3D0 loops=3D1) > Index Cond: (channel_id =3D $0) > Buffers: shared read=3D193865 I think I see the issue.. Note, this is different than before. Initially the query was slow due to reading the indices for the entire heir= archy, then sorting them, then joining: | -> Index Scan Backward using smpl_time_bx2_idx on sample_buil_year c_5(c= ost=3D0.56..1897430.89 rows=3D50597832 width=3D328) (actual time=3D0.068..1= 39840.439 rows=3D50597834 loops=3D1) | -> Index Scan Backward using smpl_time_cmx1_idx on sample_ctrl_month c_6= (cost=3D0.44..55253292.21 rows=3D18277124 width=3D85) (actual time=3D0.061= ..14610.389 rows=3D18277123 loops=3D1) | -> Index Scan Backward using smpl_time_cmx2_idx on sample_ctrl_year c_7 = (cost=3D0.57..2987358.31 rows=3D79579072 width=3D76) (actual time=3D0.067..= 286316.865 rows=3D79579075 loops=3D1) | -> Index Scan Backward using smpl_time_ux1_idx on sample_util_month c_8 = (cost=3D0.57..98830163.45 rows=3D70980976 width=3D82) (actual time=3D0.071.= .60766.643 rows=3D70980980 loops=3D1) | -> Index Scan Backward using smpl_time_ux2_idx on sample_util_year=20 |c_9 (cost=3D0.57..3070642.94 rows=3D80637888 width=3D83) (actual=20 |time=3D0.069..307091.673 rows=3D80637891 loops=3D1) Then you ANALYZEd parent tables and added indices and constraints and start= ed getting bitmap scans, with new query using David's INTERVAL '0 sec': | ... | -> Index Scan Backward using smpl_time_bx2_idx on sample_buil_year c_5 = (cost=3D0.56..2023925.30 rows=3D3162364 width=3D320) (actual time=3D15167.3= 30..15167.330 rows=3D0 loops=3D1) | Filter: (channel_id =3D $0) | Rows Removed by Filter: 50597834 | Buffers: shared hit=3D25913147 read=3D713221 | -> Index Scan Backward using sample_time_cm_idx on sample_ctrl_month c_6= (cost=3D0.56..1862587.12 rows=3D537562 width=3D77) (actual time=3D0.048..= 0.048 rows=3D0 loops=3D1) | Index Cond: (channel_id =3D $0) | Buffers: shared read=3D4 | -> Index Scan Backward using smpl_time_cmx2_idx on sample_ctrl_year c_7 = (cost=3D0.57..3186305.67 rows=3D2094186 width=3D68) (actual time=3D25847.5= 49..25847.549 rows=3D0 loops=3D1) | Filter: (channel_id =3D $0) | Rows Removed by Filter: 79579075 | Buffers: shared hit=3D49868991 read=3D1121715 | ... I didn't notice this at first, but compare the two slow scans with the fast= one. The slow scans have no index condition: they're reading the entire index an= d FILTERING on channel_id rather than searching the index for it. Now for the "bad" query you're getting: | ... | -> Index Scan Backward using sample_time_bm_idx on sample_buil_month c_4= (cost=3D0.56..2967.10 rows=3D740 width=3D74) (actual time=3D0.011..0.025 = rows=3D3 loops=3D1) | Index Cond: (channel_id =3D $0) | Buffers: shared hit=3D8 | -> Index Scan Backward using sample_time_yb1_idx on sample_buil_year c_5= (cost=3D0.56..2186210.68 rows=3D665761 width=3D75) (actual time=3D63411.5= 73..63411.574 rows=3D0 loops=3D1) | Index Cond: (channel_id =3D $0) | Buffers: shared read=3D193865 | ... This time, the bad scan *is* kind-of searching on channel_id, but reading t= he entire 1500MB index to do it ... because channel_id is not a leading col= umn: | "sample_time_yb1_idx" btree (smpl_time, channel_id) And I think the explanation is here: > css_archive_3_0_0=3D# SELECT (SELECT sum(x) FROM unnest(most_common_freqs= ) x) frac_MCV, tablename, attname, inherited, null_frac, n_distinct, array_= length(most_common_vals,1) n_mcv, array_length(histogram_bounds,1) n_hist, = correlation FROM pg_stats WHERE attname=3D'channel_id' AND tablename like '= sample%' ORDER BY 1 DESC; > frac_mcv | tablename | attname | inherited | null_frac | n_di= stinct | n_mcv | n_hist | correlation > ----------+-------------------+------------+-----------+-----------+-----= -------+-------+--------+------------- > 5e-06 | sample_buil_year | smpl_time | f | 0 | -0.= 999918 | 7 | 10001 | 0.999978 ... > 0.99987 | sample_buil_year | channel_id | f | 0 | = 76 | 16 | 60 | 0.207932 The table is highly correlated in its physical storage WRT correlation, and= poorly correlated WRT channel_id. Thats matter since it thinks the index = will be almost entirely cached, but not the table: | sample_buil_year | sample_time_yb1_idx | 4492 MB | 1522 MB So the planner thinks that reading up to 1500MB index from cache will pay o= ff in ability to read the table sequentially. If it searches the index on = channel_id, it would have to read 665761 tuples across a wide fraction of t= he table (a pages here and a page there), defeating readahead, rather than = reading pages clustered/clumped together. On Thu, Jan 03, 2019 at 12:57:27PM +0000, Abadie Lana wrote: > Main parameters : effective_cache_size : 4GB, shared_buffers 4GB,=20 > work_mem 4MB The issue here may just be that you have effective_cache_size=3D4GB, so pla= nner thinks that sample_time_yb1_idx is likely to be cached. Try decreasin= g that alot, since it's clearly not cached ? Also, effective_cache_size=3D= =3Dshared_buffers is only accurate if you've allocated the server's entire = RAM to shared_buffers, which is unreasonable. (Or perhaps if the OS cache i= s 10x busier with other processes than postgres). I'm not sure why your query plan changed with a brin indx...it wasn't actua= lly used, preferring to scan the original index on channel_id, as you hoped= . | -> Bitmap Heap Scan on sample_buil_year c_5 (cost=3D15416.21..62709= 4.50 rows=3D665761 width=3D83) (actual time=3D0.008..0.008 rows=3D0 loops= =3D1) | Recheck Cond: (channel_id =3D t.channel_id) | -> Bitmap Index Scan on sample_time_by_idx (cost=3D0.00..15249.77 ro= ws=3D665761 width=3D0) (actual time=3D0.007..0.007 rows=3D0 loops=3D1) | Index Cond: (channel_id =3D t.channel_id) = = =20 Justin Hi, First I'm using postgresql 10.5, so it means that running analyse on sample= table was also triggering a analyse on sample children. However as I said,= it is not what I observed. Analyze sample is not the same as analyse child= ren tables. Maybe because in that case it is two-level partitioning, i.e. c= hildren of children I run the tests once more with all your inputs...but this time I change the= postgres settings - but no real success Effective_cache_size=3D512MB (was 6GB) Shared_buffers=3D2GB (was 6GB) Work=3D512MB (was 4MB) original query still expensive (no filter + wrong index) : still I can see = some swap activities even though I have plenty of memory.... explain analyze select t.name, c.smpl_time,c.nanosecs,c.float_val,c.num_val= ,c.str_val,c.datatype,c.array_val from sample c, channel t where t.channel_= id=3Dc.channel_id and t.name=3D'BUIL-B36-VA-RT-RT1:CL0001-2-ABW' order by= c.smpl_time desc limit 5; = QUERY= PLAN ---------------------------------------------------------------------------= -------------------------------------------------------------- ------------------------------------------------------- Limit (cost=3D5.13..140077.00 rows=3D5 width=3D114) (actual time=3D159467= .334..1486064.079 rows=3D3 loops=3D1) -> Nested Loop (cost=3D5.13..269946514.15 rows=3D9636 width=3D114) (ac= tual time=3D159467.332..1486064.073 rows=3D3 loops=3D1) Join Filter: (c.channel_id =3D t.channel_id) Rows Removed by Join Filter: 322099471 -> Merge Append (cost=3D4.71..265115013.75 rows=3D322099464 widt= h=3D89) (actual time=3D170.874..1205525.136 rows=3D322099474 loops=3D1) Sort Key: c.smpl_time DESC -> Index Scan Backward using smpl_time_a_idx on sample c (= cost=3D0.12..8.14 rows=3D1 width=3D334) (actual time=3D0.004..0.004 ro ws=3D0 loops=3D1) -> Index Scan Backward using smpl_time_b_idx on sample_buil= c_1 (cost=3D0.42..4059177.39 rows=3D1033169 width=3D328) (actual t ime=3D14.487..13290.596 rows=3D1033169 loops=3D1) -> Index Scan Backward using smpl_time_c_idx on sample_ctrl= c_2 (cost=3D0.42..3321314.50 rows=3D942520 width=3D328) (actual ti me=3D12.598..11553.956 rows=3D942520 loops=3D1) -> Index Scan Backward using smpl_time_u_idx on sample_util= c_3 (cost=3D0.43..13064997.74 rows=3D5282177 width=3D328) (actual time=3D17.136..33692.383 rows=3D5282177 loops=3D1) -> Index Scan Backward using smpl_time_bm_idx on sample_bui= l_month c_4 (cost=3D0.43..56507719.34 rows=3D14768705 width=3D82) ( actual time=3D12.616..69994.281 rows=3D14768705 loops=3D1) -> Index Scan Backward using smpl_time_by_idx on sample_bui= l_year c_5 (cost=3D0.56..1897685.68 rows=3D50597832 width=3D84) (ac tual time=3D33.374..221346.806 rows=3D50597834 loops=3D1) -> Index Scan Backward using smpl_time_cm_idx on sample_ctr= l_month c_6 (cost=3D0.44..63167512.05 rows=3D18277124 width=3D84) ( actual time=3D17.823..80242.045 rows=3D18277123 loops=3D1) -> Index Scan Backward using smpl_time_cy_idx on sample_ctr= l_year c_7 (cost=3D0.57..2988555.40 rows=3D79579072 width=3D84) (ac tual time=3D18.082..195370.352 rows=3D79579075 loops=3D1) -> Index Scan Backward using smpl_time_um_idx on sample_uti= l_month c_8 (cost=3D0.57..110877026.27 rows=3D70980976 width=3D83) (actual time=3D26.942..184412.358 rows=3D70980980 loops=3D1) -> Index Scan Backward using smpl_time_uy_idx on sample_uti= l_year c_9 (cost=3D0.57..3075812.13 rows=3D80637888 width=3D83) (ac tual time=3D17.794..275571.960 rows=3D80637891 loops=3D1) -> Materialize (cost=3D0.41..8.44 rows=3D1 width=3D41) (actual t= ime=3D0.000..0.000 rows=3D1 loops=3D322099474) -> Index Only Scan using channel_name_channel_id_idx on cha= nnel t (cost=3D0.41..8.43 rows=3D1 width=3D41) (actual time=3D15.385. .15.388 rows=3D1 loops=3D1) Index Cond: (name =3D 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW= '::text) Heap Fetches: 1 Planning time: 1.677 ms Execution time: 1486064.165 ms (22 rows) The other query suggested by D.Rowley has the same issue : still swap activ= ity is higher. explain analyze select 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW', c.smpl_time,c.nan= osecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_val from sample c = where c.channel_id in (select channel_id from channel where name =3D'BUIL-B= 36-VA-RT-RT1:CL0001-2-ABW') order by c.smpl_time desc limit 5; = QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------- --------------------------------- Limit (cost=3D5.13..140077.00 rows=3D5 width=3D113) (actual time=3D38582.= 017..1549136.681 rows=3D3 loops=3D1) -> Nested Loop (cost=3D5.13..269946514.15 rows=3D9636 width=3D113) (ac= tual time=3D38582.014..1549136.674 rows=3D3 loops=3D1) Join Filter: (c.channel_id =3D channel.channel_id) Rows Removed by Join Filter: 322099471 -> Merge Append (cost=3D4.71..265115013.75 rows=3D322099464 widt= h=3D89) (actual time=3D0.437..1269913.701 rows=3D322099474 loops=3D1) Sort Key: c.smpl_time DESC -> Index Scan Backward using smpl_time_a_idx on sample c (= cost=3D0.12..8.14 rows=3D1 width=3D334) (actual time=3D0.006..0.006 rows=3D= 0 loops=3D1) -> Index Scan Backward using smpl_time_b_idx on sample_buil= c_1 (cost=3D0.42..4059177.39 rows=3D1033169 width=3D328) (actual time=3D0= .055..702.253 ro ws=3D1033169 loops=3D1) -> Index Scan Backward using smpl_time_c_idx on sample_ctrl= c_2 (cost=3D0.42..3321314.50 rows=3D942520 width=3D328) (actual time=3D0.= 039..684.282 row s=3D942520 loops=3D1) -> Index Scan Backward using smpl_time_u_idx on sample_util= c_3 (cost=3D0.43..13064997.74 rows=3D5282177 width=3D328) (actual time=3D= 0.045..3624.667 rows=3D5282177 loops=3D1) -> Index Scan Backward using smpl_time_bm_idx on sample_bui= l_month c_4 (cost=3D0.43..56507719.34 rows=3D14768705 width=3D82) (actual = time=3D0.039..65 099.797 rows=3D14768705 loops=3D1) -> Index Scan Backward using smpl_time_by_idx on sample_bui= l_year c_5 (cost=3D0.56..1897685.68 rows=3D50597832 width=3D84) (actual ti= me=3D0.053..1173 26.709 rows=3D50597834 loops=3D1) -> Index Scan Backward using smpl_time_cm_idx on sample_ctr= l_month c_6 (cost=3D0.44..63167512.05 rows=3D18277124 width=3D84) (actual = time=3D0.037..76 905.550 rows=3D18277123 loops=3D1) -> Index Scan Backward using smpl_time_cy_idx on sample_ctr= l_year c_7 (cost=3D0.57..2988555.40 rows=3D79579072 width=3D84) (actual ti= me=3D0.052..4150 67.696 rows=3D79579075 loops=3D1) -> Index Scan Backward using smpl_time_um_idx on sample_uti= l_month c_8 (cost=3D0.57..110877026.27 rows=3D70980976 width=3D83) (actual= time=3D0.053..1 41602.620 rows=3D70980980 loops=3D1) -> Index Scan Backward using smpl_time_uy_idx on sample_uti= l_year c_9 (cost=3D0.57..3075812.13 rows=3D80637888 width=3D83) (actual ti= me=3D0.050..3298 99.409 rows=3D80637891 loops=3D1) -> Materialize (cost=3D0.41..8.44 rows=3D1 width=3D8) (actual ti= me=3D0.000..0.000 rows=3D1 loops=3D322099474) -> Index Only Scan using channel_name_channel_id_idx on cha= nnel (cost=3D0.41..8.43 rows=3D1 width=3D8) (actual time=3D0.102..0.103 ro= ws=3D1 loops=3D1) Index Cond: (name =3D 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW= '::text) Heap Fetches: 1 Planning time: 11.566 ms Execution time: 1549156.273 ms The query which works the best so far - no swap explain analyze select 'BUIL-B36-VA-RT-RT1:CL0001-2-ABW', c.smpl_time,c.nan= osecs,c.float_val,c.num_val,c.str_val,c.datatype,c.array_val from sample c = where c.channel_id in (select channel_id from channel where name =3D'BUIL-B= 36-VA-RT-RT1:CL0001-2-ABW') order by c.smpl_time + interval '0 sec' desc = limit 5; = QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------- Limit (cost=3D1865809.94..1865809.95 rows=3D5 width=3D121) (actual time= =3D220.854..220.856 rows=3D3 loops=3D1) -> Sort (cost=3D1865809.94..1865834.03 rows=3D9636 width=3D121) (actua= l time=3D220.852..220.853 rows=3D3 loops=3D1) Sort Key: ((c.smpl_time + '00:00:00'::interval)) DESC Sort Method: quicksort Memory: 25kB -> Nested Loop (cost=3D0.00..1865649.88 rows=3D9636 width=3D121)= (actual time=3D133.087..220.823 rows=3D3 loops=3D1) -> Seq Scan on channel (cost=3D0.00..915.83 rows=3D1 width= =3D8) (actual time=3D19.561..21.602 rows=3D1 loops=3D1) Filter: ((name)::text =3D 'BUIL-B36-VA-RT-RT1:CL0001-2= -ABW'::text) Rows Removed by Filter: 33425 -> Append (cost=3D0.00..1849451.58 rows=3D1525839 width=3D= 89) (actual time=3D113.510..199.202 rows=3D3 loops=3D1) -> Seq Scan on sample c (cost=3D0.00..0.00 rows=3D1 = width=3D334) (actual time=3D0.010..0.010 rows=3D0 loops=3D1) Filter: (channel.channel_id =3D channel_id) -> Bitmap Heap Scan on sample_buil c_1 (cost=3D52.67= ..5440.29 rows=3D2096 width=3D328) (actual time=3D12.217..12.217 rows=3D0 l= oops=3D1) Recheck Cond: (channel_id =3D channel.channel_id= ) -> Bitmap Index Scan on sample_time_b_idx (cos= t=3D0.00..52.14 rows=3D2096 width=3D0) (actual time=3D12.207..12.208 rows= =3D0 loops=3D1) Index Cond: (channel_id =3D channel.channe= l_id) -> Bitmap Heap Scan on sample_ctrl c_2 (cost=3D522.3= 4..11512.86 rows=3D22441 width=3D328) (actual time=3D23.037..23.037 rows=3D= 0 loops=3D1) Recheck Cond: (channel_id =3D channel.channel_id= ) -> Bitmap Index Scan on sample_time_c_idx (cos= t=3D0.00..516.73 rows=3D22441 width=3D0) (actual time=3D23.032..23.033 rows= =3D0 loops=3D1) Index Cond: (channel_id =3D channel.channe= l_id) -> Bitmap Heap Scan on sample_util c_3 (cost=3D89.99= ..12171.59 rows=3D3814 width=3D328) (actual time=3D52.641..52.642 rows=3D0 = loops=3D1) Recheck Cond: (channel_id =3D channel.channel_id= ) -> Bitmap Index Scan on sample_time_u_idx (cos= t=3D0.00..89.04 rows=3D3814 width=3D0) (actual time=3D52.636..52.636 rows= =3D0 loops=3D1) Index Cond: (channel_id =3D channel.channe= l_id) -> Bitmap Heap Scan on sample_buil_month c_4 (cost= =3D18.28..2828.85 rows=3D738 width=3D82) (actual time=3D25.584..25.617 rows= =3D3 loops=3D1) Recheck Cond: (channel_id =3D channel.channel_id= ) Heap Blocks: exact=3D3 -> Bitmap Index Scan on sample_time_bm_idx (co= st=3D0.00..18.09 rows=3D738 width=3D0) (actual time=3D22.164..22.164 rows= =3D3 loops=3D1) Index Cond: (channel_id =3D channel.channe= l_id) -> Bitmap Heap Scan on sample_buil_year c_5 (cost=3D= 15217.21..626249.52 rows=3D657115 width=3D84) (actual time=3D15.325..15.325= rows=3D0 loops=3D1) Recheck Cond: (channel_id =3D channel.channel_id= ) -> Bitmap Index Scan on sample_by_time_idx (co= st=3D0.00..15052.93 rows=3D657115 width=3D0) (actual time=3D15.319..15.319 = rows=3D0 loops=3D1) Index Cond: (channel_id =3D channel.channe= l_id) -> Bitmap Heap Scan on sample_ctrl_month c_6 (cost= =3D4923.63..222921.32 rows=3D212525 width=3D84) (actual time=3D16.785..16.7= 86 rows=3D0 loops=3D1) Recheck Cond: (channel_id =3D channel.channel_id= ) -> Bitmap Index Scan on sample_time_cm_idx (co= st=3D0.00..4870.50 rows=3D212525 width=3D0) (actual time=3D16.779..16.779 r= ows=3D0 loops=3D1) Index Cond: (channel_id =3D channel.channe= l_id) -> Bitmap Heap Scan on sample_ctrl_year c_7 (cost=3D= 13853.69..868668.59 rows=3D598339 width=3D84) (actual time=3D21.316..21.316= rows=3D0 loops=3D1) Recheck Cond: (channel_id =3D channel.channel_id= ) -> Bitmap Index Scan on sample_cy_time_idx (co= st=3D0.00..13704.11 rows=3D598339 width=3D0) (actual time=3D21.312..21.312 = rows=3D0 loops=3D1) Index Cond: (channel_id =3D channel.channe= l_id) -> Bitmap Heap Scan on sample_util_month c_8 (cost= =3D288.92..45214.06 rows=3D12433 width=3D83) (actual time=3D17.240..17.240 = rows=3D0 loops=3D1) Recheck Cond: (channel_id =3D channel.channel_id= ) -> Bitmap Index Scan on sample_time_um_idx (co= st=3D0.00..285.81 rows=3D12433 width=3D0) (actual time=3D17.235..17.235 row= s=3D0 loops=3D1) Index Cond: (channel_id =3D channel.channe= l_id) -> Index Scan using sample_time_uy_idx on sample_util= _year c_9 (cost=3D0.57..54444.50 rows=3D16337 width=3D83) (actual time=3D1= 4.964..14.964 rows=3D0 loops=3D1) Index Cond: (channel_id =3D channel.channel_id) Planning time: 1.976 ms Execution time: 221.009 ms So it seems that the possible solutions (without a schema change on tables)= are either to drop the index and use a composite index or to use the tric= k mentioned by D.Rowley... Thanks Justin and David for your help and time, I learnt quite a lot with y= our feedback. Lana