When I run the following query in pgAdmin3, it runs in like 74ms, and I can see the execution plan but no rows are returned. I guess that’s the behavior of using EXPLAIN?
Anyway, when I remove ‘EXPLAIN’ in that query, it then is taking 3 minutes to run, anyone have a clue what the problem is?
PREPARE statement66 AS select c.car as carId, c.date as manufactured_date from v_car c where c.carId > -1 and ( c.carId = 5555 or c.truckId = 5555 or c.motorcycleId = 5555 ) order by c.date desc limit 100; EXPLAIN EXECUTE statement66;
When you run
EXPLAIN, it only returns to you the plan that the database has created for running the query. When you remove
EXPLAIN, then you are asking the database to actually perform the query.
What this implies is that, essentially, your query is going to take 3 minutes. Okay, so it’s more complicated than that…
By running a query with
EXPLAIN (ANALYZE,BUFFERS) at the beginning, you are asking the database to actually perform the full query, but rather than returning results to you, return a report about the actual query performance.
Here’s a few things to keep in mind: the first time you run a query with
EXPLAIN (ANALYZE,BUFFERS), it probably has to access data and indexes which are on disk, and so this will slow down the query for sure. However, try running the same query a second time, and most often all of the data you need to perform the query is residing in the shared buffers. Then, you’ll see the query typically finishes much more quickly on subsequent runs.
In the end,
EXPLAIN (ANALYZE,BUFFERS) is a good tool for hunting down how long your query is actually going to take, and if your query plan is any good.
I personally don’t really use prepared statements, so maybe someone else will chime in about these. I know that it may have an effect on the quality of the query planner results.