[I] Window functions seem to be very slow [sedona-db]

["kartikeyhadiya (via GitHub)" <gi...@apache.org>]

kartikeyhadiya opened a new issue, #1266:
URL: https://github.com/apache/sedona-db/issues/1266

   I was trying to port some code from duckdb to sedonadb, while geospatial functions were very fast window functions take significantly more time than expected. I tried running same query (in example below) 5 times on duckdb and sedonadb, with duckdb it took 5-8 seconds and with sedonadb it took 16-22 seconds. 
   
   ## Reproducibe python code
   ```python
   import sedona.db
   import duckdb
   
   sd = sedona.db.connect()
   dcon = duckdb.connect()
   
   query = """
   
   WITH RECURSIVE numbers AS (
       SELECT 1 AS id
       UNION ALL
       SELECT id + 1 
       FROM numbers 
       WHERE id < 100000
   ),
   mock_data AS (
       SELECT 
           id,
           'Department_' || CAST((id % 10) AS CHAR) AS department,
           ROUND(ABS(SIN(id)) * 500, 2) AS amount
       FROM numbers
   )
   SELECT 
       id,
       department,
       amount,
   
       ROW_NUMBER() OVER(PARTITION BY department ORDER BY amount DESC, id ASC) AS row_num,
       
       COUNT(*) OVER(PARTITION BY department) AS dept_total_records
   FROM mock_data
   
   LIMIT 20;
   
   """
   
   sd.sql(query).show() # 17, 18, 22, 22, 16
   dcon.sql(query) # 5, 5, 8, 8, 7
   
   ```
   
   ## Version
   sedonadb=0.4.0
   python=3.14.3
   duckdb=1.5.4


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@sedona.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] Window functions seem to be very slow [sedona-db]

["paleolimbot (via GitHub)" <gi...@apache.org>]

paleolimbot commented on issue #1266:
URL: https://github.com/apache/sedona-db/issues/1266#issuecomment-5638868601

   Thank you for opening! I wonder what the datafusion Python package would do with this (it seems like there's no spatial function involved so the SQL should work there, too). SedonaDB 0.4.0 is still on DataFusion 52.x and there may have been improvements to Window functions and/or recursive CTE evaluation since then.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@sedona.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org