Re: BUG #14948: cost overflow - Mailing list pgsql-bugs

Hi Jan,

it seems pretty likely that these are indeed overflows.  However,
because of the whitespace mangling these plans are pretty much
impossible to read.  Would you mind pasting these again, as text/plain
attachment instead?

Does the query actually take 6ms in the system with the funny explain,
and 5s on the other?

Thanks

jasc@gmx.net wrote:

> Full EXPLAIN (ANALYSE BUFFERS) on the funny looking system:
> 
> GroupAggregate 
> (cost=-25142102027752040829944596389028691968.00..-25142102027752040829944596389028691968.00
> rows=1600000000 width=254) (actual time=1.640..1.640 rows=0 loops=1)
>   Group Key: "*SELECT* 1".sendid_fk, "*SELECT* 1".campaign_fk,
> ('-1'::smallint), (('-1'::smallint)::integer)
>   Buffers: shared hit=11
>   ->  Sort 
> (cost=-25142102027752040829944596389028691968.00..-25142102027752040829944596389028691968.00
> rows=16812456500 width=120) (actual time=1.638..1.638 rows=0 loops=1)
>         Sort Key: "*SELECT* 1".sendid_fk, "*SELECT* 1".campaign_fk,
> ('-1'::smallint), (('-1'::smallint)::integer)
>         Sort Method: quicksort  Memory: 25kB
>         Buffers: shared hit=11
>         ->  Append 
> (cost=329985.38..-25142102027752040829944596389028691968.00 rows=16812456500
> width=120) (actual time=1.621..1.621 rows=0 loops=1)
>               Buffers: shared hit=11
[...]
> Planning time: 7.154 ms
> Execution time: 6.358 ms



> ##############
> # On a different system it looks like this (much more sane):
> ##############
> 
> GroupAggregate  (cost=384347.08..414413.11 rows=37349 width=254) (actual
> time=5343.214..5417.604 rows=1194 loops=1)
>   Group Key: "*SELECT* 1".sendid_fk, "*SELECT* 1".campaign_fk,
> ('-1'::smallint), (('-1'::smallint)::integer)
>   Buffers: shared hit=421793 read=26153, temp read=1561 written=1717
>   ->  Sort  (cost=384347.08..385280.81 rows=373491 width=120) (actual
> time=5343.180..5351.390 rows=55816 loops=1)
>         Sort Key: "*SELECT* 1".sendid_fk, "*SELECT* 1".campaign_fk,
> ('-1'::smallint), (('-1'::smallint)::integer)
>         Sort Method: external merge  Disk: 2880kB
>         Buffers: shared hit=421793 read=26153, temp read=1561 written=1717
>         ->  Append  (cost=140964.58..319780.25 rows=373491 width=120)
> (actual time=2319.179..5287.481 rows=55816 loops=1)
>               Buffers: shared hit=421787 read=26153, temp read=1201
> written=1356


> Planning time: 635.005 ms
> Execution time: 5423.894 ms


-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services