Re: [PERFORM] Fwd: Slow query from ~7M rows, joined to two tables of ~100 rows each

2017-06-26 Thread Jeff Janes
On Fri, Jun 23, 2017 at 1:09 PM, Chris Wilson wrote: > > The records can already be read in order from idx_metric_value If this > was selected as the primary table, and metric_pos was joined to it, then > the output would also be in order, and no sort would be needed. > > We should be able to

[PERFORM] Re: Fwd: Slow query from ~7M rows, joined to two tables of ~100 rows each

2017-06-26 Thread Karl Czajkowski
On Jun 26, Chris Wilson modulated: > I created the index starting with date and it did make a big > difference: down to 10.3 seconds using a bitmap index scan and bitmap > heap scan (and then two hash joins as before). > By the way, what kind of machine are you using? CPU, RAM, backing storage?

[PERFORM] Re: Fwd: Slow query from ~7M rows, joined to two tables of ~100 rows each

2017-06-26 Thread Karl Czajkowski
On Jun 26, Chris Wilson modulated: > ... > In your case, the equivalent hack would be to compile the small > dimension tables into big CASE statements I suppose... > > > Nice idea! I tried this but unfortunately it made the query 16 seconds > slower (up to 22 seconds) instead of faster.

Re: [PERFORM] Fwd: Slow query from ~7M rows, joined to two tables of ~100 rows each

2017-06-26 Thread Chris Wilson
Hi Karl, Thanks for the quick reply! Answers inline. My starting point, having executed exactly the preparation query in my email, was that the sample EXPLAIN (ANALYZE, BUFFERS) SELECT query ran in 15.3 seconds (best of 5), and did two nested loops . On 24 June

Re: [PERFORM] Inappropriate inner table for nested loop join

2017-06-26 Thread Albe Laurenz
Akihiko Odaki wrote: > On 2017-06-23 20:20, Albe Laurenz wrote: >> You could either try to do something like >> >> SELECT * >> FROM (SELECT "posts".* >>FROM "posts" >> JOIN "follows" ON "follows"."target_account" = "posts"."account" >>WHERE "follows"."owner_account" = $1 >