postgres min function performance
- by wutzebaer
hi i need the lowest value for runnerId
this query:
SELECT "runnerId" FROM betlog WHERE "marketId" = '107416794' ;
takes 80ms (1968 result rows)
this
SELECT min("runnerId") FROM betlog WHERE "marketId" = '107416794' ;
takes 1600ms
is there a faster way to find the minimum, or should i calc the min in my java programm?
"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?
