Slow query on partitioned table. - Mailing list pgsql-performance

Hi I am having terrible trouble with a simple partitioned table.
Select queries are very slow.

Ie

SELECT ts::timestamptz, s1.sensor_id, s1.value
                                  FROM sensor_values s1
                                   WHERE s1.sensor_id =
ANY(ARRAY[596304,597992,610978,597998])
                                         AND s1.ts >= '2000-01-01
00:01:01'::timestamptz AND
                                         s1.ts < '2018-03-20
00:01:01'::timestamptz

Takes over five minutes.


Postgres version is PostgreSQL 9.5.9 on x86_64-pc-linux-gnu, compiled
by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
shared_buffers = 3000MB
work_mem = 50MB
maintenance_work_mem = 64MB
wal_writer_delay = 10000ms
#effective_cache_size = 4GB  I guess this is the default

My amount of memory is 15G


The table gets constant inserts (thousands a minute)
The table has something like 700000000 rows.

So the table is defined as


\d+ sensor_values;
                                               Table "public.sensor_values"
  Column   |           Type           |                 Modifiers
            | Storage | Stats target | Description

-----------+--------------------------+--------------------------------------------+---------+--------------+-------------
 ts        | timestamp with time zone | not null
            | plain   |              |
 value     | double precision         | not null default 'NaN'::real
            | plain   |              |
 sensor_id | integer                  | not null
            | plain   |              |
 status    | tridium_status           | not null default
'unknown'::tridium_status | plain   |              |
Indexes:
    "sensor_values_sensor_id_timestamp_index" UNIQUE, btree (sensor_id, ts)
Foreign-key constraints:
    "sensor_values_sensor_id_fkey" FOREIGN KEY (sensor_id) REFERENCES
sensors(id)
Triggers:
    a_statistics_trigger BEFORE INSERT OR DELETE ON sensor_values FOR
EACH ROW EXECUTE PROCEDURE stat_info()
    sensor_values_trigger_timestamp_sensor_insert_sensor_values BEFORE
INSERT ON sensor_values FOR EACH ROW EXECUTE PROCEDURE
sensor_values_timestamp_sensor_func_insert_trigger()
Child tables: sensor_values_2007q1,
              sensor_values_2007q2,
              sensor_values_2007q3,
              sensor_values_2007q4,
              sensor_values_2008q1,
              sensor_values_2008q2,
              sensor_values_2008q3,
              sensor_values_2008q4,
              sensor_values_2009q1,
              sensor_values_2009q2,
              sensor_values_2009q3,
              sensor_values_2009q4,
              sensor_values_2010q1,
              sensor_values_2010q2,
              sensor_values_2010q3,
              sensor_values_2010q4,
              sensor_values_2011q1,
              sensor_values_2011q2,
              sensor_values_2011q3,
              sensor_values_2011q4,
              sensor_values_2012q1,
              sensor_values_2012q2,
              sensor_values_2012q3,
              sensor_values_2012q4,
              sensor_values_2013q1,
              sensor_values_2013q2,
              sensor_values_2013q3,
              sensor_values_2013q4,
              sensor_values_2014q1,
              sensor_values_2014q2,
              sensor_values_2014q3,
              sensor_values_2014q4,
              sensor_values_2015q1,
              sensor_values_2015q2,
              sensor_values_2015q3,
              sensor_values_2015q4,
              sensor_values_2016q1,
              sensor_values_2016q2,
              sensor_values_2016q3,
              sensor_values_2016q4,
              sensor_values_2017q1,
              sensor_values_2017q2,
              sensor_values_2017q3,
              sensor_values_2017q4,
              sensor_values_2018q1,
              sensor_values_2018q2,
              sensor_values_2018q3,
              sensor_values_2018q4,
              sensor_values_2019q1,
              sensor_values_2019q2,
              sensor_values_2019q3,
              sensor_values_2019q4,
              sensor_values_2020q1,
              sensor_values_2020q2,
              sensor_values_2020q3,
              sensor_values_2020q4



The child tables are all like

  Column   |           Type           |                 Modifiers
            | Storage | Stats target | Description

-----------+--------------------------+--------------------------------------------+---------+--------------+-------------
 ts        | timestamp with time zone | not null
            | plain   |              |
 value     | double precision         | not null default 'NaN'::real
            | plain   |              |
 sensor_id | integer                  | not null
            | plain   |              |
 status    | tridium_status           | not null default
'unknown'::tridium_status | plain   |              |
Indexes:
    "sensor_values_2018q1_sensor_id_timestamp_index" UNIQUE, btree
(sensor_id, ts)
Check constraints:
    "sensor_values_2018q1_timestamp_check" CHECK (ts >= '2018-01-01
00:00:00+00'::timestamp with time zone AND ts < '2018-04-01
01:00:00+01'::timestamp with time zone)
Inherits: sensor_values





EXPLAIN (ANALYZE, BUFFERS)  SELECT ts::timestamptz, s1.sensor_id, s1.value
                                  FROM sensor_values s1
                                   WHERE s1.sensor_id =
ANY(ARRAY[596304,597992,610978,597998])
                                         AND s1.ts >= '2000-01-01
00:01:01'::timestamptz AND
                                         s1.ts < '2018-03-20
00:01:01'::timestamptz
[2018-03-27 14:45:39] 260 rows retrieved starting from 1 in 13m 13s
221ms (execution: 13m 13s 141ms, fetching: 80ms)


Shows the following output


https://explain.depesz.com/s/c8HU



Any idea why this query takes so long ?

Thanks