My application will receive selected queries submitted by users, but instead of executing it and getting a full set of results, at this stage it just needs column names and types.
Users will be data analysts, so I expect a lot of hairy queries to be submitted. Submitted requests will be stored in the application and often evaluated (based on a mixture of triggers and clones) as the amount of data increases.
The client application is written in python and sqlalchemy-core to interact with the database.
I am currently adding LIMIT 0at the end of the query to get only metadata for the results. This gives me acceptable results.
Example:
request to be interrogated:
SELECT * FROM users
trial request
SELECT * FROM users LIMIT 0
EXPLAIN ANALYZE :
Limit (cost=0.00..0.11 rows=1 width=646) (actual time=0.001..0.001 rows=0 loops=1)
-> Seq Scan on users (cost=0.00..22457.28 rows=207728 width=646) (never executed)
Planning time: 0.067 ms
Execution time: 0.025 ms
, Seq Scan on users (never executed).
LIMIT 0 :
EXPLAIN ANALYZE
WITH blah AS (
SELECT *
FROM users
JOIN reservation
ON reservation.user_id = users.id
)
SELECT * FROM blah
LIMIT 0
Limit (cost=482925.94..482925.96 rows=1 width=1955) (actual time=0.001..0.001 rows=0 loops=1)
CTE blah
-> Hash Join (cost=42094.88..482925.94 rows=1563750 width=1418) (never executed)
Hash Cond: (reservation.user_id = users.id)
-> Seq Scan on reservation (cost=0.00..96868.50 rows=1563750 width=772) (never executed)
-> Hash (cost=22457.28..22457.28 rows=207728 width=646) (never executed)
-> Seq Scan on users (cost=0.00..22457.28 rows=207728 width=646) (never executed)
-> CTE Scan on blah (cost=0.00..31275.00 rows=1563750 width=1955) (never executed)
Planning time: 8.284 ms
Execution time: 0.113 ms
, .
:
?
Postgresql , CTE . , LIMIT 0, CTE, , (never executed). , (9.5 +)
/ ? 1 , .
anecdotally, SELECT?
, , , . , ?