search

ankane / dexter

The automatic indexer for Postgres
MIT License
1.9k stars 47 forks source link

"Could not run explain" on queries that can be manually #51

Closed kwongtn closed 7 months ago

kwongtn commented 8 months ago

The following queries show a "could not run explain" when run via dexter:

Query fe79b362d324b6da
Total time: 0.0 min, avg time: 0 ms, calls: 1
Could not run explain

SELECT COUNT(*) AS "__count" FROM "notification_notification" WHERE ("notification_notification"."tenant_account_id" = xx AND "notification_notification"."agent_id" = xxx AND "notification_notification"."id" = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'::uuid AND "notification_notification"."agent_id" = xxx)

However it returns results when run manually using EXPLAIN ANALYZE:

Aggregate  (cost=4.17..4.18 rows=1 width=8) (actual time=0.033..0.034 rows=1 loops=1)
  ->  Index Scan using chat_notification_tenant_account_id_2edd2b1e on notification_notification  (cost=0.14..4.17 rows=1 width=0) (actual time=0.027..0.028 rows=0 loops=1)
        Index Cond: (tenant_account_id = xxx)
        Filter: ((agent_id = xxx) AND (id = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'::uuid))
Planning Time: 0.276 ms
Execution Time: 0.089 ms

Help? 😂

ankane commented 8 months ago

Hi @kwongtn, you can use the --log-explain option to get the error message.

kwongtn commented 8 months ago

@ankane this is what it shows -- I presume that if it is just a single layer it will show "could not run explain"? 

[sql] EXPLAIN (FORMAT JSON) 
SELECT COUNT(*) AS "__count" FROM "notification_notification" WHERE ("notification_notification"."tenant_account_id" = xxx AND "notification_notification"."agent_id" = xxx AND "notification_notification"."id" = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'::uuid AND "notification_notification"."agent_id" = xxx)
[sql] EXPLAIN (FORMAT TEXT) 
SELECT COUNT(*) AS "__count" FROM "notification_notification" WHERE ("notification_notification"."tenant_account_id" = xxx AND "notification_notification"."agent_id" = xxx AND "notification_notification"."id" = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'::uuid AND "notification_notification"."agent_id" = xxx)
Aggregate  (cost=4.17..4.18 rows=1 width=8)
  ->  Index Scan using chat_notification_tenant_account_id_2edd2b1e on notification_notification  (cost=0.14..4.17 rows=1 width=0)
        Index Cond: (tenant_account_id = xxx)
        Filter: ((agent_id = xxx) AND (id = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'::uuid))
ankane commented 8 months ago

EXPLAIN (FORMAT TEXT) should show up 3 times for the query, so it's likely happening in another part of the output.