Duckdb: Running map() over fetchdf() column with and without LIMIT has huge difference

Created on 9 Jul 2020  路  16Comments  路  Source: cwida/duckdb

Data Frame map() over single column of roughly 1m rows with and without LIMIT huge difference

Thank you for implementing this awesome DB! Here is something that may turn out to be bug and help tune performance with queries without LIMIT clause.

With a source Parquet file including 1000001 (1m+1) rows runs very slow without LIMIT. Running on OSX with Python3.8.

Time: 1min 44s

con.execute("SELECT numCol FROM parquet_scan('table.parquet')").fetchdf().map(lambda x: x.rank(pct=True))

Time: 2s

con.execute("SELECT numCol FROM parquet_scan('table.parquet') LIMIT 1000001").fetchdf().map(lambda x: x.rank(pct=True))

==> Running the query without LIMIT makes it run very slow (1m41s) and seems to make Python hog CPU core 100% for the time it is running. I would expect both queries to have similar complexity and execution time. Both queries give same results.

picture dforsber

All 16 comments

Very interesting. Thanks for the bug report! Does the Parquet file have other columns besides numCol? My initial thoughts are that this could be related to the projection pushdown not being performed correctly, which would result in the entire Parquet file being (unnecessarily) loaded.

Mytherin picture Mytherin on 10 Jul 2020

Very interesting. Thanks for the bug report! Does the Parquet file have other columns besides numCol? My initial thoughts are that this could be related to the projection pushdown not being performed correctly, which would result in the entire Parquet file being (unnecessarily) loaded.

Yes, the Parquet file has probably around 200 columns with size roughly 600MB.

Btw. I ran the same thing on AWS Lambda and it seg.faulted there, but Lambda reported low memory consumption (<200MB), which sounds a bit confusing. After that I ran it in my 2y old mb pro laptop with ssd. Reading 600MB from SSD shouldn't take that long by far (1m44s).

How may I help you with this further? :)

dforsber picture dforsber on 10 Jul 2020

Weak signal: not sure this is at all related. But creating multiple VIEWs over separate Parquet files and running UNION queries over all of them (use case: Hive Table partitions/buckets in separate Parquet files) is really slow compared to single VIEW queries (over Parquet file also).

dforsber picture dforsber on 10 Jul 2020

How may I help you with this further? :)

Providing the parquet file or a way to generate the parquet file would help :) Could also be by e-mail if you prefer not to put it online.

Weak signal: not sure this is at all related. But creating multiple VIEWs over separate Parquet files and running UNION queries over all of them (use case: Hive Table partitions/buckets in separate Parquet files) is really slow compared to single VIEW queries (over Parquet file also).

This is probably related to UNION requiring duplicate elimination which is not yet especially optimized. Using UNION ALL should be a lot faster if you just want to concatenate them.

Mytherin picture Mytherin on 10 Jul 2020

How may I help you with this further? :)

Providing the parquet file or a way to generate the parquet file would help :) Could also be by e-mail if you prefer not to put it online.

Weak signal: not sure this is at all related. But creating multiple VIEWs over separate Parquet files and running UNION queries over all of them (use case: Hive Table partitions/buckets in separate Parquet files) is really slow compared to single VIEW queries (over Parquet file also).

I'm afraid I can't provide the data as it is from production. I have compiled DuckDB from trunk with default settings though, I can run some tests and compile it with debug switches if needed. I was thinking that maybe some PRs already fixed this issue, but then didn't know how to make the Python module out of my head.

This is probably related to UNION requiring duplicate elimination which is not yet especially optimized. Using UNION ALL should be a lot faster if you just want to concatenate them.

I'm sure I tested UNION ALL as well, but will re-test :).

dforsber picture dforsber on 10 Jul 2020

Did UNION ALL resolve this?

hannesmuehleisen picture hannesmuehleisen on 21 Jul 2020
👀1

Did UNION ALL resolve this?

  • p1 is a view of partition 1
  • p2 is a view of partition 2
    start = time.time()
    print(con.execute("SELECT COUNT(*) FROM (SELECT * FROM p1) AS c;").fetchall())
    print("COUNT p1: ", time.time() - start)
    start = time.time()
    print(con.execute("SELECT COUNT(*) FROM (SELECT * FROM p2) AS c;").fetchall())
    print("COUNT p2: ", time.time() - start)
    start = time.time()
    print(con.execute("SELECT COUNT(*) FROM (SELECT * FROM p3) AS c;").fetchall())
    print("COUNT p3: ", time.time() - start)
    start = time.time()
    print(con.execute("SELECT COUNT(*) FROM (SELECT * FROM p1 UNION ALL SELECT * FROM p2) AS c;").fetchall())
    print("COUNT p1+p2: ", time.time() - start)

[(4634010,)]
COUNT p1:  0.21184420585632324
[(4479486,)]
COUNT p2:  0.1878979206085205
[(9113496,)]
COUNT p1+p2:  13.913604021072388

==> I guess the data is read from disk when UNION ALL is used. First queries over single partition VIEWs probably just read the Parquet metadata (?).

dforsber picture dforsber on 30 Jul 2020

Did UNION ALL resolve this?

  • p1 is a view of partition 1
  • p2 is a view of partition 2
    start = time.time()
    print(con.execute("SELECT COUNT(*) FROM (SELECT * FROM p1) AS c;").fetchall())
    print("COUNT p1: ", time.time() - start)
    start = time.time()
    print(con.execute("SELECT COUNT(*) FROM (SELECT * FROM p2) AS c;").fetchall())
    print("COUNT p2: ", time.time() - start)
    start = time.time()
    print(con.execute("SELECT COUNT(*) FROM (SELECT * FROM p3) AS c;").fetchall())
    print("COUNT p3: ", time.time() - start)
    start = time.time()
    print(con.execute("SELECT COUNT(*) FROM (SELECT * FROM p1 UNION ALL SELECT * FROM p2) AS c;").fetchall())
    print("COUNT p1+p2: ", time.time() - start)

[(4634010,)]
COUNT p1:  0.21184420585632324
[(4479486,)]
COUNT p2:  0.1878979206085205
[(9113496,)]
COUNT p1+p2:  13.913604021072388

==> I guess the data is read from disk when UNION ALL is used. First queries over single partition VIEWs probably just read the Parquet metadata (?).

When using DuckDB 0.2.1 pre-release and GZIP compressed Parquet files the timings are similar. But if I create the view over a single column instead of *, then the results come faster, less to read from the disk:

[(4634010,)]
COUNT p1:  0.14461398124694824
[(4479486,)]
COUNT p2:  0.140761137008667
[(9113496,)]
COUNT p1+p2:  1.3763880729675293

==> Would be nice to have "pushdown" to Parquet working over VIEWs combined with UNION ALL in a way that the perf of p1+p2 would be equal to perf of p1 + perf of p2.

dforsber picture dforsber on 30 Jul 2020

Thanks for the detailed reports! Indeed this seems like a pushdown bug that needs to be fixed. I am planning on unifying the different scan operators next week, which I think might resolve this. I will use your queries as tests to verify that pushdown is correctly performed when I implement this and come back to you.

Mytherin picture Mytherin on 30 Jul 2020
👍1

Thanks for the detailed reports! Indeed this seems like a pushdown bug that needs to be fixed. I am planning on unifying the different scan operators next week, which I think might resolve this. I will use your queries as tests to verify that pushdown is correctly performed when I implement this and come back to you.

Thank you. Would you need any testing that would help you?

dforsber picture dforsber on 1 Sep 2020

Thank you. Would you need any testing that would help you?

That won't be necessary, apologies, this got put on the back burner :) I will have another look soon. When it's finished I will let you know and you can take a look to see if your issue is resolved.

Mytherin picture Mytherin on 2 Sep 2020
👍1

Could you verify that #883 fixed this when you have time? Thanks!

Mytherin picture Mytherin on 7 Sep 2020
👀1

For the UNION ALL perf test, with duckdb==0.2.2.dev82:

Count from p1
[{"count()":4383403}]
0.5650150775909424
Count from p2
[{"count()":4383403}]
0.16575884819030762
Count from p1+p2
[{"count()":8766806}]
6.632367134094238
Count from p1+p2+p3
[{"count()":13400816}]
10.27719521522522
Count from p1+p2+p3+p4
[{"count()":17880302}]
12.90190601348877
Count from p1+p2+p3+p4+p5
[{"count()":22553533}]
18.06703519821167

But this is the same issue as https://github.com/cwida/duckdb/issues/773

dforsber picture dforsber on 11 Sep 2020

For the case with LIMIT and without LIMIT, I think the big difference is now solved. So, IMO can close this ticket :).

Count from p1
[{"count()":4383403}]
0.4346020221710205
----------------------------
With transform (*2) without LIMIT
         tag_id
0        649834
1        649832
2        649830
3        649828
4        649826
...         ...
4383398  521494
4383399  521492
4383400  521490
4383401  521488
4383402  521486

[4383403 rows x 1 columns]
1.7706449031829834
With transform (*2) with LIMIT
         tag_id
0        649834
1        649832
2        649830
3        649828
4        649826
...         ...
4383398  521494
4383399  521492
4383400  521490
4383401  521488
4383402  521486

[4383403 rows x 1 columns]
1.6768078804016113
dforsber picture dforsber on 11 Sep 2020

For the UNION ALL perf test, with duckdb==0.2.2.dev82:

Count from p1
[{"count()":4383403}]
0.5650150775909424
Count from p2
[{"count()":4383403}]
0.16575884819030762
Count from p1+p2
[{"count()":8766806}]
6.632367134094238
Count from p1+p2+p3
[{"count()":13400816}]
10.27719521522522
Count from p1+p2+p3+p4
[{"count()":17880302}]
12.90190601348877
Count from p1+p2+p3+p4+p5
[{"count()":22553533}]
18.06703519821167

But this is the same issue as #773

Interesting, nice find! Are the queries SELECT COUNT(*) FROM p1 vs SELECT COUNT(*) FROM (SELECT * FROM p1 UNION ALL SELECT * FROM p2)? Will have a look.

Mytherin picture Mytherin on 11 Sep 2020

For the UNION ALL perf test, with duckdb==0.2.2.dev82:

Count from p1
[{"count()":4383403}]
0.5650150775909424
Count from p2
[{"count()":4383403}]
0.16575884819030762
Count from p1+p2
[{"count()":8766806}]
6.632367134094238
Count from p1+p2+p3
[{"count()":13400816}]
10.27719521522522
Count from p1+p2+p3+p4
[{"count()":17880302}]
12.90190601348877
Count from p1+p2+p3+p4+p5
[{"count()":22553533}]
18.06703519821167

But this is the same issue as #773

Interesting, nice find! Are the queries SELECT COUNT(*) FROM p1 vs SELECT COUNT(*) FROM (SELECT * FROM p1 UNION ALL SELECT * FROM p2)? Will have a look.

Yes: SELECT COUNT(*) FROM (SELECT * FROM p1 UNION ALL SELECT * FROM p2) as d;. Both p1 and p2 are VIEWs over Parquet files.

dforsber picture dforsber on 11 Sep 2020
Was this page helpful?
0 / 5 - 0 ratings

Related issues

asad-awadia picture asad-awadia  路  8Comments
mrigger picture mrigger  路  5Comments
Mytherin picture Mytherin  路  5Comments
hannesmuehleisen picture hannesmuehleisen  路  7Comments
GuillaumePressiat picture GuillaumePressiat  路  6Comments