Hi Maarten, Thanks for the reproducible script. I ran it on my laptop on pyarrow master, and not seeing the difference between both datetime indexes:
Versions: Python: 3.7.3 | packaged by conda-forge | (default, Mar 27 2019, 23:01:00) [GCC 7.3.0] on linux numpy: 1.16.4 pandas: 0.26.0.dev0+447.gc168ecf26 pyarrow: 0.14.1.dev642+g7f2d637db 1073741824 float64 8388608 16 0: make_dataframe : 1443.483 msec, 709 MB/s 0: write_arrow_parquet : 7685.426 msec, 133 MB/s 0: read_arrow_parquet : 1262.741 msec, 811 MB/s <<< 1: make_dataframe : 1412.575 msec, 725 MB/s 1: write_arrow_parquet : 7869.145 msec, 130 MB/s 1: read_arrow_parquet : 1947.896 msec, 526 MB/s <<< 2: make_dataframe : 1490.165 msec, 687 MB/s 2: write_arrow_parquet : 7040.507 msec, 145 MB/s 2: read_arrow_parquet : 1888.316 msec, 542 MB/s <<< The only change I needed to make in the script to get it running (within my memory limits) was the creation of the second DatetimeIndex (pd.date_range('1970-01-01', '2019-09-01', freq='S') creates an index of 1.5 billion elements, while only the last part of it is used. So changed that to index = pd.date_range('2018-01-01', '2019-09-01', freq='S').array[-rows:]) The datetime index reading in general is still slower as the int index. But doing a bit more detailed timings, and it seems this is not due to the reading of parquet, but the conversion of arrow to pandas (using the files from the benchmark): In [1]: import pyarrow.parquet as pq In [4]: %timeit pq.read_table('testdata.int.parquet') 41.5 ms ± 3.31 ms per loop (mean ± std. dev. of 7 runs, 10 loops each) In [5]: %timeit pq.read_table('testdata.dt.parquet') 43 ms ± 1.75 ms per loop (mean ± std. dev. of 7 runs, 10 loops each) In [6]: table_int = pq.read_table('testdata.int.parquet') In [7]: table_datetime = pq.read_table('testdata.dt.parquet') In [8]: %timeit table_int.to_pandas() 14.3 ms ± 309 µs per loop (mean ± std. dev. of 7 runs, 100 loops each) In [9]: %timeit table_datetime.to_pandas() 47.2 ms ± 2.53 ms per loop (mean ± std. dev. of 7 runs, 10 loops each) So you can see that the parquet reading part is basically identical, but the conversion to pandas is much slower for the datetime-index case. I will try to look into that code path to see what makes this so much slower. Joris On Tue, 24 Sep 2019 at 22:28, Maarten Ballintijn <maart...@xs4all.nl> wrote: > Hi, > > The code to show the performance issue with DateTimeIndex is at: > > https://gist.github.com/maartenb/256556bcd6d7c7636d400f3b464db18c > > It shows three case 0) int index, 1) datetime index, 2) date time index > created in a slightly roundabout way > > I’m a little confused by the two date time cases. Case 2) is much slower > but the df compares identical to case 1) > (I originally used something like 2) to match our specific data. I don’t > see why it behaves differently??) > > The timings I find are: > > 1073741824 float64 8388608 16 > 0: make_dataframe : 2390.830 msec, 428 MB/s > 0: write_arrow_parquet : 2486.463 msec, 412 MB/s > 0: read_arrow_parquet : 813.946 msec, 1258 MB/s <<< > 1: make_dataframe : 2579.815 msec, 397 MB/s > 1: write_arrow_parquet : 2708.151 msec, 378 MB/s > 1: read_arrow_parquet : 1413.999 msec, 724 MB/s <<< > 2: make_dataframe : 15126.520 msec, 68 MB/s > 2: write_arrow_parquet : 9205.815 msec, 111 MB/s > 2: read_arrow_parquet : 5929.346 msec, 173 MB/s <<< > > Case 0, int index. This is all great. > Case 1, date time index. We loose almost half the speed. Given that a > datetime is only scaled from Pandas IIRC that seems like a lot? > Case 3, other datetime index. No idea what is going on. > > Any insights are much appreciated. > > Cheers, > Maarten. > > > On Sep 24, 2019, at 11:25 AM, Wes McKinney <wesmck...@gmail.com> wrote: > > > > hi > > > > On Tue, Sep 24, 2019 at 9:26 AM Maarten Ballintijn <maart...@xs4all.nl > <mailto:maart...@xs4all.nl>> wrote: > >> > >> Hi Wes, > >> > >> Thanks for your quick response. > >> > >> Yes, we’re using Python 3.7.4, from miniconda and conda-forge, and: > >> > >> numpy: 1.16.5 > >> pandas: 0.25.1 > >> pyarrow: 0.14.1 > >> > >> It looks like 0.15 is close, so I can wait for that. > >> > >> Theoretically I see three components driving the performance: > >> 1) The cost of locating the column (directory overhead) > >> 2) The overhead of reading a single column. (reading and processing > meta data, setting up for reading) > >> 3) Bulk reading and unmarshalling/decoding the data. > >> > >> Only 1) would be impacted by the number of columns, but if you’re > reading everything ideally this would not be a problem. > > > > The problem is more nuanced than that. Parquet's metadata is somewhat > > "heavy" at the column level. So when you're writing thousands of > > columns, the fixed overhead associated with reading a single column > > becomes problematic. There are several data structures associated with > > decoding a column have a fixed setup and teardown cost. Even if there > > is 1 millisecond of fixed overhead related to reading a column (I > > don't know what the number is exactly) then reading 10,000 columns has > > 10 seconds of unavoidable overhead. It might be useful for us to > > quantify and communicate the expected overhead when metadata and > > decoding is taken into account. Simply put having more than 1000 > > columns is not advisable. > > > >> Based on an initial cursory look at the Parquet format I guess the > index and the column meta-data might need to be read in full so I can see > how that might slow down reading only a few columns out of a large set. But > that was not really the case here? > >> > >> What would you suggest for looking into the date index slow-down? > > > > Can you show a code example to make things easier for us to see what > > you're seeing? > > > >> > >> Cheers, > >> Maarten. > >> > >> > >> > >>> On Sep 23, 2019, at 7:07 PM, Wes McKinney <wesmck...@gmail.com> wrote: > >>> > >>> hi Maarten, > >>> > >>> Are you using the master branch or 0.14.1? There are a number of > >>> performance regressions in 0.14.0/0.14.1 that are addressed in the > >>> master branch, to appear as 0.15.0 relatively soon. > >>> > >>> As a file format, Parquet (and columnar formats in general) is not > >>> known to perform well with more than 1000 columns. > >>> > >>> On the other items, we'd be happy to work with you to dig through the > >>> performance issues you're seeing. > >>> > >>> Thanks > >>> Wes > >>> > >>> On Mon, Sep 23, 2019 at 5:52 PM Maarten Ballintijn <maart...@xs4all.nl> > wrote: > >>>> > >>>> Greetings, > >>>> > >>>> We have Pandas DataFrames with typically about 6,000 rows using > DateTimeIndex. > >>>> They have about 20,000 columns with integer column labels, and data > with a dtype of float32. > >>>> > >>>> We’d like to store these dataframes with parquet, using the ability > to read a subset of columns and to store meta-data with the file. > >>>> > >>>> We’ve found the reading performance less than expected compared to > the published benchmarks (e.g. Wes’ blog post). > >>>> > >>>> Using a modified version of his script we did reproduce his results > (~ 1GB/s for high entropy, no dict on MacBook pro) > >>>> > >>>> But there seem to be three factors that contribute to the slowdown > for our datasets: > >>>> > >>>> - DateTimeIndex is much slower then a Int index (we see about a > factor 5). > >>>> - The number of columns impact reading speed significantly (factor ~2 > going from 16 to 16,000 columns) > >>>> - The ‘use_pandas_metadata=True’ slows down reading significantly and > appears unnecessary? (about 40%) > >>>> > >>>> Are there ways we could speedup the reading? Should we use a > different layout? > >>>> > >>>> Thanks for your help and insights! > >>>> > >>>> Cheers, > >>>> Maarten > >>>> > >>>> > >>>> ps. the routines we used: > >>>> > >>>> def write_arrow_parquet(df: pd.DataFrame, fname: str) -> None: > >>>> table = pa.Table.from_pandas(df) > >>>> pq.write_table(table, fname, use_dictionary=False, compression=None) > >>>> return > >>>> > >>>> def read_arrow_parquet(fname: str) -> pd.DataFrame: > >>>> table = pq.read_table(fname, use_pandas_metadata=False, > use_threads=True) > >>>> df = table.to_pandas() > >>>> return df > >