StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhy is row count 0 in my PostgreSQL plan?
text
Body
copied!<p>I have a query which is equi-joining two tables, TableA and TableB using a nested loop. Because of the "equi"-join contraint, all rows returned in the result will therefore correspond to at least one row from each of these two tables. However, according to the plan (EXPLAIN ANALYZE) the actual rows count is 0 from TableB, even though a row is returned in the final result. How can the <em>actual rows</em> count equal zero here?</p> <p>Here is the execution plan:</p> <pre><code>=> explain analyze select p.id, p.title, s.count from products p, stock s where p.id = s.p_id and s.w_id = 6 and p.type = 9 and s.count > 0 order by p.title; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Sort (cost=42.42..42.42 rows=2 width=36) (actual time=0.198..0.199 rows=1 loops=1) Sort Key: p.title Sort Method: quicksort Memory: 25kB -> Nested Loop (cost=0.00..42.41 rows=2 width=36) (actual time=0.170..0.181 rows=1 loops=1) -> Seq Scan on products p (cost=0.00..9.25 rows=4 width=32) (actual time=0.068..0.106 rows=4 loops=1) Filter: (type = 9) -> Index Scan using stock_pk on stock s (cost=0.00..8.28 rows=1 width=8) (actual time=0.015..0.015 rows=0 loops=4) Index Cond: ((w_id = 6) AND (p_id = p.id)) Filter: (count > 0) Total runtime: 0.290 ms </code></pre> <p>And the two table definitions... The products table first:</p> <pre><code>=> \d products Table "public.products" Column | Type | Modifiers --------+------------------------+----------- id | integer | not null title | character varying(100) | type | integer | price | double precision | filler | character(500) | Indexes: "products_pkey" PRIMARY KEY, btree (id) "products_type_idx" btree (type) Referenced by: TABLE "orderline" CONSTRAINT "orderline_p_id_fkey" FOREIGN KEY (p_id) REFERENCES products(id) TABLE "stock" CONSTRAINT "stock_p_id_fkey" FOREIGN KEY (p_id) REFERENCES products(id) </code></pre> <p>The stock table:</p> <pre><code>=> \d stock Table "public.stock" Column | Type | Modifiers --------+---------+----------- w_id | integer | not null p_id | integer | not null count | integer | Indexes: "stock_pk" PRIMARY KEY, btree (w_id, p_id) "stock_p_id_idx" btree (p_id) Foreign-key constraints: "stock_p_id_fkey" FOREIGN KEY (p_id) REFERENCES products(id) "stock_w_id_fkey" FOREIGN KEY (w_id) REFERENCES warehouses(id) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload