Postgresql select max performance I have very simple function:

CREATE OR REPLACE FUNCTION myscheme.get_last_timecode(ptable_ids integer[], ptimecode bigint DEFAULT NULL::bigint)
  RETURNS bigint AS
$BODY$
  SELECT MAX(timecode) AS timecode
  FROM myscheme.event
  WHERE table_id = ANY($1) AND ($2 IS NULL OR timecode &lt;= $2);
$BODY$
  LANGUAGE sql STABLE
  COST 100;


Count of rows:

SELECT COUNT(*) FROM myscheme.event WHERE table_id = 1; -- 120
SELECT COUNT(*) FROM myscheme.event WHERE table_id = 2; -- 18
SELECT COUNT(*) FROM myscheme.event WHERE table_id = 3; -- 839795


The results are quite unexpected:

EXPLAIN ANALYZE SELECT myscheme.get_last_timecode(ARRAY[1], NULL) -- Total runtime: 212.552 ms
EXPLAIN ANALYZE SELECT myscheme.get_last_timecode(ARRAY[2], NULL) -- Total runtime: 213.713 ms
EXPLAIN ANALYZE SELECT myscheme.get_last_timecode(ARRAY[3], NULL) -- Total runtime: 0.186 ms (the fastest!)


When I use plain query, execution time is normal:

EXPLAIN ANALYZE SELECT MAX(timecode) AS timecode FROM myscheme.event
  WHERE table_id = ANY(ARRAY[1]) AND (NULL IS NULL OR timecode &lt;= NULL);

Aggregate  (cost=10.18..10.19 rows=1 width=8) (actual time=0.079..0.079 rows=1 loops=1)
  -&gt;  Index Scan using event_table_id_index on event  (cost=0.42..10.05 rows=51 width=8) (actual time=0.013..0.067 rows=120 loops=1)
        Index Cond: (table_id = ANY ('{1}'::integer[]))
Total runtime: 0.101 ms


but it uses another execution plan for table_id = 3:

EXPLAIN ANALYZE SELECT MAX(timecode) AS timecode FROM poker.event
  WHERE table_id = ANY(ARRAY[3]) AND (NULL IS NULL OR timecode &lt;= NULL);

Result  (cost=0.47..0.48 rows=1 width=0) (actual time=0.018..0.018 rows=1 loops=1)
  InitPlan 1 (returns $0)
    -&gt;  Limit  (cost=0.42..0.47 rows=1 width=8) (actual time=0.015..0.016 rows=1 loops=1)
          -&gt;  Index Scan Backward using event_timecode_key on event  (cost=0.42..35873.11 rows=845301 width=8) (actual time=0.015..0.015 rows=1 loops=1)
                Index Cond: (timecode IS NOT NULL)
                Filter: (table_id = ANY ('{3}'::integer[]))
Total runtime: 0.038 ms


Can anybody explain me how to create a function (or index) whose execution time will not depend on the amount of data?

SELECT version();
PostgreSQL 9.3.14 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit


And the table definition:

CREATE TABLE myscheme.event
(
  id bigserial NOT NULL,
  table_id integer,
  deal_id bigint,
  type_id integer NOT NULL,
  timecode bigserial NOT NULL,
  created timestamp with time zone NOT NULL DEFAULT now(),
  parent_id bigint,
  prev_id bigint,
  CONSTRAINT event_pkey PRIMARY KEY (id),
  CONSTRAINT event_deal_id_fkey FOREIGN KEY (deal_id)
      REFERENCES myscheme.deal (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT event_parent_id_fkey FOREIGN KEY (parent_id)
      REFERENCES myscheme.event (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT event_prev_id_fkey FOREIGN KEY (prev_id)
      REFERENCES myscheme.event (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT event_table_id_fkey FOREIGN KEY (table_id)
      REFERENCES myscheme.tables (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT event_type_id_fkey FOREIGN KEY (type_id)
      REFERENCES myscheme.event_type (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT event_timecode_key UNIQUE (timecode)
)
WITH (
  OIDS=FALSE
);

CREATE INDEX event_deal_id_index ON myscheme.event USING btree (deal_id);
CREATE INDEX event_table_id_index ON myscheme.event  USING btree (table_id);
CREATE INDEX event_type_id_index ON myscheme.event USING btree (type_id);
if you look at the statistics the execution plans make perfect sense. 
The table has around 840k tuples almost all of table id 3. 
So if you're looking for max(timecode) scanning the timecode index backwards makes perfect sense with table id 3 (almost every line you hit has that id). 

But if you're looking for table id 1 - you have only 120 tuples out of 840k, your best bet is to scan these 120 tuples looking for the  max(timecode). 

It's like looking for a needle in a hay stack - looking for hay is simpler than looking for the needle. 

Hope it made some sense into what the optimizer planned. 

Btw, you could try to build a composite index of table id and timecode - I'm not near a db to check but I think PG could use that to skip to the max timecode per table id directly. 
- worth a shot :)

Regards 
Jony