Retrieving raster data is taking forever

Question

I am trying to execute queries through Python.

Unfortunately, it takes forever. In fact, executing the same query in the DBeaver takes around 5 secs. In addition, queries such as create table, and alter table work perfectly. When it comes to this query it sticks:

def connection_db():
    """
    Establish connection to the DB
    :param db_name: Database name

    :return: cursor, engine

    """

    # connect to database
    conn = psycopg2.connect()
    conn.autocommit = True
    cursor = conn.cursor()
    
    engine = create_engine()

    
    return cursor, engine


cursor, engine = connection_db()

test = """
SELECT ST_DumpAsPolygons(rast.rast,1) band1, ST_DumpAsPolygons(rast.rast,2) band2,
ST_DumpAsPolygons(rast.rast,3) band3, ST_DumpAsPolygons(rast.rast,4) band4,
ST_DumpAsPolygons(rast.rast,5) band5, ST_DumpAsPolygons(rast.rast,6) band6,
ST_DumpAsPolygons(rast.rast,7) band7
FROM raster_grid g, public.t32ulc_entire_tile rast
WHERE rast.rid = 1
"""
print('here')
cursor.execute(test)
    # Fetch result
bbox = cursor.fetchone()[0]

What is wrong with my query?

The raster size is 2.3 GB.

Answer 1

You haven't told the database where the end of the query is. It's waiting for more sql. You need to terminate your query with a semicolon:

test = """
SELECT ST_DumpAsPolygons(rast.rast,1) band1, ST_DumpAsPolygons(rast.rast,2) band2,
ST_DumpAsPolygons(rast.rast,3) band3, ST_DumpAsPolygons(rast.rast,4) band4,
ST_DumpAsPolygons(rast.rast,5) band5, ST_DumpAsPolygons(rast.rast,6) band6,
ST_DumpAsPolygons(rast.rast,7) band7
FROM raster_grid g, public.t32ulc_entire_tile rast
WHERE rast.rid = 1;
"""