EXPLAIN EXECUTE completes quickly, but is slow without EXPLAIN

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…

Try EXPLAIN (ANALYZE,BUFFERS)

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.