BUG #11120: Decrease in no. of rows while sorting - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      11120
Logged by:          Pankhuri
Email address:      pankhuri.sai@gmail.com
PostgreSQL version: 9.3.4
Operating system:   Ubuntu
Description:

n executing the following query, it is found that no. of actual rows after
hash join of part and partsupp (158960) is not equal to no. of rows sorted
after that(158755).

PostgreSQL stand-alone backend 9.0.4
backend> explain analyze select
s_acctbal,s_name,n_name,p_partkey,p_mfgr,s_address,s_phone,s_comment from
part,supplier,partsupp,nation,region where p_partkey = ps_partkey and
s_suppkey = ps_suppkey and p_size = 15 and p_type like '%BRASS' and
s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name =
'EUROPE' and ps_supplycost = (select min(ps_supplycost) from partsupp,
supplier,nation, region where p_partkey = ps_partkey and s_suppkey =
ps_suppkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and
r_name = 'EUROPE') order by s_acctbal desc,n_name,s_name,p_partkey limit
100;

     1: QUERY PLAN    (typeid = 25, len = -1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "Limit  (cost=110757.89..110757.90 rows=1 width=194)
(actual time=1351.746..1351.797 rows=100 loops=1)"    (typeid = 25, len = -1,
typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "  ->  Sort  (cost=110757.89..110757.90 rows=1 width=194)
(actual time=1351.745..1351.764 rows=100 loops=1)"    (typeid = 25, len = -1,
typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "        Sort Key: public.supplier.s_acctbal,
public.nation.n_name, public.supplier.s_name, part.p_partkey"    (typeid = 25,
len = -1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "        Sort Method:  top-N heapsort  Memory:
43kB"    (typeid = 25, len = -1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "        ->  Merge Join  (cost=71733.26..110757.88 rows=1
width=194) (actual time=977.223..1350.155 rows=460 loops=1)"    (typeid = 25,
len = -1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "              Merge Cond: (part.p_partkey =
public.partsupp.ps_partkey)"    (typeid = 25, len = -1, typmod = -1, byval =
f)
    ----
     1: QUERY PLAN = "              Join Filter: (public.partsupp.ps_supplycost
= (SubPlan 1))"    (typeid = 25, len = -1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "              ->  Index Scan using part_pkey on part
(cost=0.00..9866.27 rows=691 width=30) (actual time=0.196..123.455 rows=747
loops=1)"    (typeid = 25, len = -1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "                    Filter: (((p_type)::text ~~
'%BRASS'::text) AND (p_size = 15))"    (typeid = 25, len = -1, typmod = -1,
byval = f)
    ----
     1: QUERY PLAN = "              ->  Materialize  (cost=71733.20..72533.20
rows=160000 width=176) (actual time=976.597..1123.234 rows=158755
loops=1)"    (typeid = 25, len = -1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "                    ->  Sort  (cost=71733.20..72133.20
rows=160000 width=176) (actual time=976.591..1058.967 rows=158755
loops=1)"    (typeid = 25, len = -1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "                          Sort Key:
public.partsupp.ps_partkey"    (typeid = 25, len = -1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "                          Sort Method:  external sort
Disk: 29688kB"    (typeid = 25, len = -1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "                          ->  Hash Join
(cost=407.03..30554.03 rows=160000 width=176) (actual time=11.338..649.054
rows=158960 loops=1)"    (typeid = 25, len = -1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "                                Hash Cond:
(public.partsupp.ps_suppkey = public.supplier.s_suppkey)"    (typeid = 25, len
= -1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "                                ->  Seq Scan on partsupp
(cost=0.00..25547.00 rows=800000 width=16) (actual time=0.003..260.071
rows=800000 loops=1)"    (typeid = 25, len = -1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "                                ->  Hash
(cost=382.03..382.03 rows=2000 width=168) (actual time=11.305..11.305
rows=1987 loops=1)"    (typeid = 25, len = -1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "                                      Buckets: 1024
Batches: 1  Memory Usage: 373kB"    (typeid = 25, len = -1, typmod = -1, byval
= f)
    ----
     1: QUERY PLAN = "                                      ->  Hash Join
(cost=2.53..382.03 rows=2000 width=168) (actual time=0.055..9.361 rows=1987
loops=1)"    (typeid = 25, len = -1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "                                            Hash Cond:
(public.supplier.s_nationkey = public.nation.n_nationkey)"    (typeid = 25, len
= -1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "                                            ->  Seq Scan
on supplier  (cost=0.00..322.00 rows=10000 width=146) (actual
time=0.001..4.341 rows=10000 loops=1)"    (typeid = 25, len = -1, typmod = -1,
byval = f)
    ----
     1: QUERY PLAN = "                                            ->  Hash
(cost=2.47..2.47 rows=5 width=30) (actual time=0.040..0.040 rows=5
loops=1)"    (typeid = 25, len = -1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "
Buckets: 1024  Batches: 1  Memory Usage: 1kB"    (typeid = 25, len = -1, typmod
= -1, byval = f)
    ----
     1: QUERY PLAN = "                                                  ->
Hash Join  (cost=1.07..2.47 rows=5 width=30) (actual time=0.024..0.036
rows=5 loops=1)"    (typeid = 25, len = -1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "
Hash Cond: (public.nation.n_regionkey = public.region.r_regionkey)"    (typeid
= 25, len = -1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "
->  Seq Scan on nation  (cost=0.00..1.25 rows=25 width=34) (actual
time=0.001..0.013 rows=25 loops=1)"    (typeid = 25, len = -1, typmod = -1,
byval = f)
    ----
     1: QUERY PLAN = "
->  Hash  (cost=1.06..1.06 rows=1 width=4) (actual time=0.010..0.010 rows=1
loops=1)"    (typeid = 25, len = -1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "
    Buckets: 1024  Batches: 1  Memory Usage: 1kB"    (typeid = 25, len = -1,
typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "
    ->  Seq Scan on region  (cost=0.00..1.06 rows=1 width=4) (actual
time=0.006..0.007 rows=1 loops=1)"    (typeid = 25, len = -1, typmod = -1,
byval = f)
    ----
     1: QUERY PLAN = "
          Filter: (r_name = 'EUROPE'::bpchar)"    (typeid = 25, len = -1,
typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "              SubPlan 1"    (typeid = 25, len = -1, typmod =
-1, byval = f)
    ----
     1: QUERY PLAN = "                ->  Aggregate  (cost=49.02..49.03 rows=1
width=8) (actual time=0.056..0.057 rows=1 loops=642)"    (typeid = 25, len =
-1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "                      ->  Nested Loop  (cost=0.00..49.02
rows=1 width=8) (actual time=0.032..0.053 rows=2 loops=642)"    (typeid = 25,
len = -1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "                            Join Filter:
(public.nation.n_regionkey = public.region.r_regionkey)"    (typeid = 25, len =
-1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "                            ->  Seq Scan on region
(cost=0.00..1.06 rows=1 width=4) (actual time=0.002..0.003 rows=1
loops=642)"    (typeid = 25, len = -1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "                                  Filter: (r_name =
'EUROPE'::bpchar)"    (typeid = 25, len = -1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "                            ->  Nested Loop
(cost=0.00..47.91 rows=4 width=12) (actual time=0.020..0.047 rows=4
loops=642)"    (typeid = 25, len = -1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "                                  ->  Nested Loop
(cost=0.00..46.78 rows=4 width=12) (actual time=0.017..0.034 rows=4
loops=642)"    (typeid = 25, len = -1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "                                        ->  Index Scan
using partsupp_pkey on partsupp  (cost=0.00..13.66 rows=4 width=12) (actual
time=0.012..0.014 rows=4 loops=642)"    (typeid = 25, len = -1, typmod = -1,
byval = f)
    ----
     1: QUERY PLAN = "                                              Index Cond:
($0 = ps_partkey)"    (typeid = 25, len = -1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "                                        ->  Index Scan
using supplier_pkey on supplier  (cost=0.00..8.27 rows=1 width=8) (actual
time=0.003..0.004 rows=1 loops=2568)"    (typeid = 25, len = -1, typmod = -1,
byval = f)
    ----
     1: QUERY PLAN = "                                              Index Cond:
(public.supplier.s_suppkey = public.partsupp.ps_suppkey)"    (typeid = 25, len
= -1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "                                  ->  Index Scan using
nation_pkey on nation  (cost=0.00..0.27 rows=1 width=8) (actual
time=0.002..0.002 rows=1 loops=2568)"    (typeid = 25, len = -1, typmod = -1,
byval = f)
    ----
     1: QUERY PLAN = "                                        Index Cond:
(public.nation.n_nationkey = public.supplier.s_nationkey)"    (typeid = 25, len
= -1, typmod = -1, byval = f)
    ----
     1: QUERY PLAN = "Total runtime: 1357.918 ms"    (typeid = 25, len = -1,
typmod = -1, byval = f)
    ----
backend> backend>