Re: [GENERAL] Why so long? - Mailing list pgsql-general

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()