Understanding the simple execution plan in PostgreSQL

Question

I'm new to execution plans still trying to learn. My problem is for below query

select * from item limit 3;

I got following execution plan,

"Limit (cost=0.00..0.07 rows=3 width=75)" 
" -> Seq Scan on item (cost=0.00..2345.00 rows=100000 width=75)"

According to my understanding this first limit the number of records to 3 and then do a sequential scan for all the records. Why does this do a scan for all the records since it has already limited the result to 3 rows ?

• Craig Ringer: It's a little surprising that the plan estimate shows rows=100000 on the inner node, as I thought the planner was smart enough to push down rowcount estimates. What PostgreSQL version is this?
• Viraj: This is version 8.4

Answer 1

According to my understanding this first limit the number of records to 3 and then do a sequential scan for all the records. Why does this do a scan for all the records since it has already limited the result to 3 rows ?

That's not (necessarily) what's happening.

Outer nodes consume rows from inner nodes one by one with a demand-pull model.

So in this case, the limit node will result in a maximum of three nodes being consumed from the inner sequential scan node. As a sequential scan node can deliver nodes progressively it will do very little work.

This won't be true of some other node types. For example, if you add an ORDER BY (which you always should with LIMIT otherwise the results can be pretty much random) it will add a Sort or top-N sort node. A Sort node must fully execute the entire subplan before it returns any results, so it might do a lot of work even if there's a limit node on the outside. A top-N sort node can sometimes avoid some of that work, but it depends on the inner plan.

If you use EXPLAIN (ANALYZE, VERBOSE) you can see the actual row counts processed by each node, which is usually informative. In this case the actual rows processed by the inner seqscan will probably be 3.

You will find http://explain.depesz.com/ extremely useful when reading plans. PgAdmin-III also has a graphical explain that some people like, though I don't use it myself.