Search Postgresql Archives

Re: Why so long?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux