Re: Why so long?

Should add this is version 9.4.10 of postgresql

On 04/19/2017 11:24 AM, Steve Clark wrote:
> Hello,
>
> I am confused. I have a table that has an incrementing primary key id.
>
> When I select max(id) from table is returns almost instantly but
> when I select min(id) from table it takes longer than I want to wait.
>
> Shouldn't postgresql be able to quickly find the minimum id value in the index?
>
>
> pmacct=# explain select max(id) from netflow;
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------
> Result (cost=1.13..1.14 rows=1 width=0)
> InitPlan 1 (returns $0)
> -> Limit (cost=0.71..1.13 rows=1 width=8)
> -> Index Only Scan Backward using netflow_pkey on netflow (cost=0.71..3799108784.10 rows=9123246080 width=8)
> Index Cond: (id IS NOT NULL)
> (5 rows)
>
> pmacct=# explain select min(id) from netflow;
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------
> Result (cost=1.13..1.14 rows=1 width=0)
> InitPlan 1 (returns $0)
> -> Limit (cost=0.71..1.13 rows=1 width=8)
> -> Index Only Scan using netflow_pkey on netflow (cost=0.71..3799108784.10 rows=9123246080 width=8)
> Index Cond: (id IS NOT NULL)
> (5 rows)
>
> pmacct=# \timing
> Timing is on.
> pmacct=# select max(id) from netflow;
> max
> -------------
> 17547256873
> (1 row)
>
> Time: 0.626 ms
>
>
>
> pmacct=# select min(id) from netflow;
> ^CCancel request sent
> ERROR: canceling statement due to user request
> Time: 339114.334 ms
>
>
> Table "public.netflow"
> Column | Type | Modifiers
> ----------------+-----------------------------+---------------------------------------------------------------------
> id | bigint | not null default nextval('netflow_id_seq'::regclass)
> agent_id | bigint |
> bytes | bigint |
> stamp_inserted | timestamp without time zone | not null default '0001-01-01 00:00:00'::timestamp without time zone
> stamp_updated | timestamp without time zone |
> packets | integer | default 0
> port_src | integer | default 0
> port_dst | integer | default 0
> ip_proto | smallint | default 0
> tos | smallint | default 0
> ip_src | inet | not null default '0.0.0.0'::inet
> ip_dst | inet | not null default '0.0.0.0'::inet
> Indexes:
> "netflow_pkey" PRIMARY KEY, btree (id)
> "netflow_ts_key" btree (stamp_inserted)
> "netflow_tsu_idx" btree (stamp_updated)
> Triggers:
> netflow_import AFTER INSERT OR UPDATE ON netflow FOR EACH STATEMENT EXECUTE PROCEDURE netflow_update()
>
>
>
> --
>

--
Stephen Clark
*NetWolves Managed Services, LLC.*
Director of Technology
Phone: 813-579-3200
Fax: 813-882-0209
Email: steve(dot)clark(at)netwolves(dot)com
http://www.netwolves.com