postgres min function performance

Posted by wutzebaer on Stack Overflow See other posts from Stack Overflow or by wutzebaer
Published on 2012-11-24T22:34:20Z Indexed on 2012/11/24 23:04 UTC
Read the original article Hit count: 130

Filed under:
|

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?

© Stack Overflow or respective owner

Related posts about Performance

Related posts about postgresql