Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Optimizing Top K in Postgres

paradedb.com

Post reply on HN
Print view View original

11–20 of 26 posts

Re: Optimizing Top K in Postgres

#11

Post by Cervisia »

jmgimeno wrote: post #6

Maybe I'm wrong, but for this query: SELECT * FROM benchmark_logs WHERE severity this index CREATE INDEX ON benchmark_logs (severity, timestamp); cannot be used as proposed: "Postgres can jump directly to the portion of the tree matching severity Postgres with this index can walk to a part of the tree with severity < 3, but timestamps are sorted only for the same severity.

The SQLite documentation explains how (and how well) this works: https://www.sqlite.org/optoverview.html#the_skip_scan_optimi...

Re: Optimizing Top K in Postgres

#12

Post by igorw »

Cervisia wrote: post #11
jmgimeno wrote: post #6

Maybe I'm wrong, but for this query: SELECT * FROM benchmark_logs WHERE severity this index CREATE INDEX ON benchmark_logs (severity, timestamp); cannot be used as proposed: "Postgres can jump directly to the portion of the tree matching severity Postgres with this index can walk to a part of the tree with severity < 3, but timestamps are sorted only for the same severity.

The SQLite documentation explains how (and how well) this works: https://www.sqlite.org/optoverview.html#the_skip_scan_optimi...

While Postgres did introduce skip scan in 18, it only works for equality matching: https://www.crunchydata.com/blog/get-excited-about-postgres-...

Re: Optimizing Top K in Postgres

#13

Post by h1fra »

Postgres is really good at a lot of things, but it's very unfortunate that it's really bad at simple analytics. I wish there was a plugin instead of having to have N databases

Re: Optimizing Top K in Postgres

#14

Post by mattashii »

dragon96 wrote: post #8
jmgimeno wrote: post #6

Maybe I'm wrong, but for this query: SELECT * FROM benchmark_logs WHERE severity this index CREATE INDEX ON benchmark_logs (severity, timestamp); cannot be used as proposed: "Postgres can jump directly to the portion of the tree matching severity Postgres with this index can walk to a part of the tree with severity < 3, but timestamps are sorted only for the same severity.

If severity is a low cardinality enum, it still seems acceptable

The order returned from the Index Scan is not the ordering requested by the user, so there would still have to be a full (or topk) Sort over the dataset returned from the index scan, which could negate the gains you get from using an Index Scan; PostgreSQL itself does not produce merge join plans that merge a spread of index scans to get suffix-ordered data out of an index.

Re: Optimizing Top K in Postgres

#17

Post by Tostino »

jmgimeno wrote: post #6

Maybe I'm wrong, but for this query: SELECT * FROM benchmark_logs WHERE severity this index CREATE INDEX ON benchmark_logs (severity, timestamp); cannot be used as proposed: "Postgres can jump directly to the portion of the tree matching severity Postgres with this index can walk to a part of the tree with severity < 3, but timestamps are sorted only for the same severity.

Do a partial index on just timestamp where severity < 3 instead.

Re: Optimizing Top K in Postgres

#18

Post by ajstars »

Curious whether you benchmarked against a partial index on the sort column. For fixed-category top-K queries the planner sometimes picks it over a full index scan, though I've seen it regress on high-write tables due to index bloat. Did write volume factor into your test setup?

Re: Optimizing Top K in Postgres

#20

Post by philippemnoel »

esafak wrote: post #19
h1fra wrote: post #13

Postgres is really good at a lot of things, but it's very unfortunate that it's really bad at simple analytics. I wish there was a plugin instead of having to have N databases

ParadeDB is a pair of postgres plugins.

(ParadeDB maintainer here) Yes! We've already built some faster analytics in Postgres, and have a lot more coming. Here's some relevant context in case you're curious: https://www.paradedb.com/blog/faceting
Post reply on HN