hdf5 loading performance issue for non-sorted data in pandas 1.0.1

[leo4183]

Problem Description

compared to the legacy pandas 0.2x.x, the most recent 1.0.1 version doesnt perform well in HDF5 file loading unless the conditioned column was sorted beforehand

import os
import numpy as np
import pandas as pd
row = 6000
col = 2
start_date = '20000101'
freq = 'B'
ind = pd.date_range(start_date,periods=row,freq=freq)
a = pd.DataFrame(np.random.rand(row*col).reshape((row,-1)),index=ind)
a = a.reset_index(drop=False)
a.columns = ['date','x','y']
a = pd.concat([a]*2000)
a.sort_values('y').to_hdf(os.path.join(os.path.expanduser('~'),'hdf_test.h5'),key='df',data_columns=True,format='table')
a.sort_values('date').to_hdf(os.path.join(os.path.expanduser('~'),'hdf_test_sorted.h5'),key='df',data_columns=True,format='table')

# significantly slower in pandas 1.0.1 (this is not an issue in pandas 0.2x.x)
store = pd.HDFStore(os.path.join(os.path.expanduser('~'),'hdf_test.h5'),mode='r')
result = store.select('df',where="{0}>=20150101 & {0}<=20160730".format('date'),)
store.close()

# as fast as pandas 0.2x.x if data was sorted on the 'term' column
store = pd.HDFStore(os.path.join(os.path.expanduser('~'),'hdf_test_sorted.h5'),mode='r')
result = store.select('df',where="{0}>=20150101 & {0}<=20160730".format('date'),)
store.close()

[TomAugspurger]

Can you provide more information, like the actual timings?

Have you profiled things to see what's slower? Perhaps bisect to find what commit slowed things down?

[leo4183]

actual timing as follows (using the generated df described before):

In[1]: store = pd.HDFStore(os.path.join(os.path.expanduser('~'),'hdf_test.h5'),mode='r')
In[2]: %timeit result = store.select('df',where="{0}>=20150101 & {0}<=20160730".format('date'),)
       5.93 s ± 52.1 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
In[3]: store.close()

In[4]: store = pd.HDFStore(os.path.join(os.path.expanduser('~'),'hdf_test_sorted.h5'),mode='r')
In[5]: %timeit result = store.select('df',where="{0}>=20150101 & {0}<=20160730".format('date'),)
       918 ms ± 125 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
In[6]: store.close()

[TomAugspurger]

And where is the slowdown at? I'd recommend using https://jiffyclub.github.io/snakeviz/ to get an idea.

[leo4183]

notice that the performance issue is with pandas 1.0.1 (and pytable 3.6.1). pandas 0.2x.x with pytable 3.6.1 turns out to be fine.

# prun for non-sorted hdf data
ncalls  tottime  percall  cumtime  percall filename:lineno(function)
     1    4.946    4.946    4.946    4.946 {method '_read_elements' of 'tables.tableextension.Table' objects}
     1    0.762    0.762    0.784    0.784 table.py:1541(<listcomp>)
    89    0.090    0.001    0.090    0.001 {built-in method numpy.array}
     1    0.064    0.064    5.950    5.950 pytables.py:5089(select)
     2    0.023    0.011    0.023    0.011 {method '_read_index_slice' of 'tables.indexesextension.IndexArray' objects}
     1    0.020    0.020    5.054    5.054 table.py:1966(read_coordinates)

# prun for sorted hdf data
ncalls  tottime  percall  cumtime  percall filename:lineno(function)
     1    1.223    1.223    1.233    1.233 table.py:1541(<listcomp>)
    88    0.180    0.002    0.181    0.002 {built-in method numpy.array}
     1    0.135    0.135    1.605    1.605 pytables.py:5089(select)
     3    0.021    0.007    0.021    0.007 {method '_read_index_slice' of 'tables.indexesextension.IndexArray' objects}
     1    0.019    0.019    0.019    0.019 {method '_read_records' of 'tables.tableextension.Table' objects}
     4    0.014    0.004    0.015    0.004 numeric.py:2287(array_equal)
     1    0.012    0.012    0.033    0.033 index.py:2004(get_chunkmap)
     3    0.011    0.004    0.011    0.004 {method 'copy' of 'numpy.ndarray' objects}
   164    0.008    0.000    0.008    0.000 {method '_g_getattr' of 'tables.hdf5extension.AttributeSet' objects}
    26    0.008    0.000    0.008    0.000 conditions.py:445(call_on_recarr)
   139    0.005    0.000    0.005    0.000 {method 'reduce' of 'numpy.ufunc' objects}
    67    0.005    0.000    0.005    0.000 {method '_g_get_objinfo' of 'tables.hdf5extension.Group' objects}
    18    0.005    0.000    0.006    0.000 {method '_open_array' of 'tables.hdf5extension.Array' objects}
    19    0.003    0.000    0.003    0.000 {method '_g_get_lchild_attr' of 'tables.hdf5extension.Group' objects}
   183    0.003    0.000    0.021    0.000 attributeset.py:295(__getattr__)
  2609    0.003    0.000    0.004    0.000 {built-in method builtins.isinstance}
     1    0.002    0.002    1.470    1.470 table.py:1527(read_where)