On Fri, Nov 16, 2018 at 08:38:26AM -0600, Justin Pryzby wrote:
> Are you referring to this a future commit ?
this OR a future commit
> The table is not too special, but was probably ALTERed to add columns a good
> number of times by one of our processes. It has ~1100 columns, including
> arrays, and some with null_frac=1. I'm trying to come up with a test case
> involving column types and order.
I don't have a failing test case yet but here's what the columns look like:
ts=# SELECT attnum, null_frac, atttypid::regtype, attnotnull, attname, attalign
, attstorage, attbyval, attlen, attislocal FROM pg_stats s JOIN pg_attribute a
USING(attname) JOIN pg_class c ON s.tablename=c.relname WHERE c.oid=a.attrelid
AND tablename='daily_eric_umts_rnc_utrancell_view_201804' AND (attnotnull OR
null_frac>0.9 OR atttypid::regtype::text LIKE '%[]' OR NOT
atttypid::regtype::text~'int$|integer|double|numeric' OR attlen=-1 OR NOT
attbyval OR atthasmissing OR attisdropped OR attnum BETWEEN 80 AND 99) ORDER BY
1;
attnum | null_frac | atttypid | attnotnull |
attname | attalign | attstorage | attbyval | attlen |
attislocal
--------+-----------+-----------------------------+------------+-------------------------------------------------+----------+------------+----------+--------+------------
1 | 0 | timestamp without time zone | t | start_time
| d | p | t | 8 | t
2 | 0 | integer | t | site_id
| i | p | t | 4 | t
3 | 0 | integer | t | sect_id
| i | p | t | 4 | t
4 | 0 | integer | t | rnc_id
| i | p | t | 4 | t
5 | 0 | text | t | utrancell
| i | x | f | -1 | t
30 | 1 | bigint | f |
dl_alt_chcode_alloc | d | p | t
| 8 | t
31 | 1 | integer | f |
dl_alt_chcode_alloc_min | i | p | t
| 4 | t
32 | 1 | integer | f |
dl_alt_chcode_alloc_max | i | p | t
| 4 | t
45 | 0 | integer[] | f |
dch_ul_rlc_user_tput_samples | i | x | f
| -1 | t
46 | 0 | integer[] | f |
dch_ul_rlc_user_tput_samples_min | i | x | f
| -1 | t
47 | 0 | integer[] | f |
dch_ul_rlc_user_tput_samples_max | i | x | f
| -1 | t
51 | 0 | numeric | f |
ps_int_sum_latency_2 | i | m | f
| -1 | t
69 | 0 | numeric | f |
mbytes_ul_srb_only_eul | i | m | f
| -1 | t
[...]
87 | 0 | numeric | f |
mbytes_dl_active_cs57 | i | m | f
| -1 | t
88 | 0 | numeric | f |
mbytes_dl_active_cs57_min | i | m | f
| -1 | t
89 | 0 | numeric | f |
mbytes_dl_active_cs57_max | i | m | f
| -1 | t
If I query for cs57, it doesen't crash (in 500ms), but if I query for the next
column, cs57_min, it does (in 18000ms).
Here's a new error message instead of a crash this time:
ts=# SET jit=on;SET jit_above_cost=0;explain(analyze on,verbose off) SELECT
b.mbytes_dl_active_cs57_min FROM
child.daily_eric_umts_rnc_utrancell_view_201804 a JOIN
child.daily_eric_umts_rnc_utrancell_view_201804 b USING(start_time,sect_id)
WHERE a.start_time BETWEEN '2018-04-30' AND '2018-05-04' AND b.start_time
BETWEEN '2018-04-30' AND '2018-05-04';
SET
SET
ERROR: out of memory
DETAIL: Failed on request of size 425170160 in memory context
"HashBatchContext".
Here's verbose output you requested, sans expressions:
ts=# SET jit=on;SET jit_above_cost=0;explain(analyze off,verbose) SELECT
b.mbytes_dl_active_cs57_min FROM
child.daily_eric_umts_rnc_utrancell_view_201804 a JOIN
child.daily_eric_umts_rnc_utrancell_view_201804 b USING(start_time,sect_id)
WHERE a.start_time BETWEEN '2018-04-30' AND '2018-05-04' AND b.start_time
BETWEEN '2018-04-30' AND '2018-05-04';
SET
SET
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=2224.45..4491.19 rows=22 width=3)
Output: b.mbytes_dl_active_cs57_min
Hash Cond: ((a.start_time = b.start_time) AND (a.sect_id = b.sect_id))
-> Index Only Scan using
daily_eric_umts_rnc_utrancell_view_201804_unique_idx on
child.daily_eric_umts_rnc_utrancell_view_201804 a (cost=0.29..2214.33 rows=656
width=12)
Output: a.start_time, a.site_id, a.rnc_id, a.sect_id, a.utrancell
Index Cond: ((a.start_time >= '2018-04-30 00:00:00'::timestamp without
time zone) AND (a.start_time <= '2018-05-04 00:00:00'::timestamp without time
zone))
-> Hash (cost=2214.33..2214.33 rows=656 width=15)
Output: b.mbytes_dl_active_cs57_min, b.start_time, b.sect_id
-> Index Scan using
daily_eric_umts_rnc_utrancell_view_201804_unique_idx on
child.daily_eric_umts_rnc_utrancell_view_201804 b (cost=0.29..2214.33 rows=656
width=15)
Output: b.mbytes_dl_active_cs57_min, b.start_time, b.sect_id
Index Cond: ((b.start_time >= '2018-04-30 00:00:00'::timestamp
without time zone) AND (b.start_time <= '2018-05-04 00:00:00'::timestamp
without time zone))
JIT:
Functions: 19
Options: Inlining false, Optimization false, Expressions true, Deforming true
If I query instead for a.mbytes_dl_active_cs57_min, I get no crash (yet).
Justin
