Postgres min function performance

Question

I need the lowest value for runnerId.

This query:

SELECT "runnerId" FROM betlog WHERE "marketId" = '107416794' ;

takes 80 ms (1968 result rows).

This:

SELECT min("runnerId") FROM betlog WHERE "marketId" = '107416794' ;

takes 1600 ms.

Is there a faster way to find the minimum, or should I calc the min in my java program?

"Result  (cost=100.88..100.89 rows=1 width=0)"
"  InitPlan 1 (returns $0)"
"    ->  Limit  (cost=0.00..100.88 rows=1 width=9)"
"          ->  Index Scan using runneridindex on betlog  (cost=0.00..410066.33 rows=4065 width=9)"
"                Index Cond: ("runnerId" IS NOT NULL)"
"                Filter: ("marketId" = 107416794::bigint)"

CREATE INDEX marketidindex
  ON betlog
  USING btree
  ("marketId" COLLATE pg_catalog."default");

Another idea:

SELECT "runnerId" FROM betlog WHERE "marketId" = '107416794' ORDER BY "runnerId" LIMIT 1 >1600ms
SELECT "runnerId" FROM betlog WHERE "marketId" = '107416794' ORDER BY "runnerId" >>100ms

How can a LIMIT slow the query down?

Answer 1

What you need is a multi-column index:

CREATE INDEX betlog_mult_idx ON betlog ("marketId", "runnerId");

If interested, you'll find in-depth information about multi-column indexes in PostgreSQL, links and benchmarks under this related question on dba.SE.

How did I figure?
In a multi-column index, rows are ordered (and thereby clustered) by the first column of the index ("marketId"), and each cluster is in turn ordered by the second column of the index - so the first row matches the condition min("runnerId"). This makes the index scan extremely fast.

Concerning the paradox effect of LIMIT slowing down a query - the Postgres query planner has a weakness there. The common workaround is to use a CTE (not necessary in this case). Find more information under this recent, closely related question:
PostgreSQL query taking too long