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.cl...@netwolves.com http://www.netwolves.com