Skip site navigation (1) Skip section navigation (2)

BUG #6587: Limit on a query is mis-documented

From: mboldi(at)prospectiv(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #6587: Limit on a query is mis-documented
Date: 2012-04-13 19:40:35
Message-ID: E1SImMF-0001YR-06@wrigleys.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged on the website:

Bug reference:      6587
Logged by:          Mike Boldi
Email address:      mboldi(at)prospectiv(dot)com
PostgreSQL version: 9.0.3
Operating system:   Linux/Redhat
Description:        

in the manual it says
If a limit count is given, no more than that many rows will be returned (but
possibly less, if the query itself yields less rows)
BUT !!  limit caps the largest internal spool. not the rows returned.
I am doing a large query so I limit the query to 5500000 but I get 88 rows
returned. I increase this to 9000000 and get 136 rows returned ..
explain Plan ...
Aggregate  (cost=1689951.65..1689951.66 rows=1 width=0)
   ->  Hash Join  (cost=644.62..1687656.17 rows=918194 width=0)
         Hash Cond: (a.email_a = o.email)
         ->  Subquery Scan on a  (cost=0.00..1654854.18 rows=9149 width=64)
               Filter: ((a.memberid_a > a.memberid_b) AND (a.email_a =
a.email_b) AND (length(a.email_a) > 0))
               ->  Limit  (cost=0.00..1325501.10 rows=16467654 width=690)
                     ->  Nested Loop  (cost=0.00..1325501.10 rows=16467654
width=690)
                           ->  Seq Scan on lgen_fraud_members12282 
(cost=0.00..1597.14 rows=5381 width=345)
                                 Filter: ((regcomplete <> (-8)) AND (dateid
>= 5938))
                           ->  Index Scan using lgah11_12282 on
lgen_fraud_members12282  (cost=0.00..123.62 rows=3060 width=345)
                                 Index Cond:
((staging.lgen_fraud_members12282.dateid >=
staging.lgen_fraud_members12282.dateid) AND
(staging.lgen_fraud_members12282.memberid >
staging.lgen_fraud_members12282.memberid))
         ->  Hash  (cost=393.72..393.72 rows=20072 width=32)
               ->  Seq Scan on lgen_fraud_email o  (cost=0.00..393.72
rows=20072 width=32)



Responses

pgsql-bugs by date

Next:From: Robert HaasDate: 2012-04-13 22:53:48
Subject: Re: BUG #6572: The example of SPI_execute is bogus
Previous:From: Tom LaneDate: 2012-04-13 18:53:47
Subject: Re: BUG #6585: Can't compile from source code

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group