Re: Give me a HINT or I'll got crazy ;)

In the madness I did

- setting statistics target to 1000 for all join & filter columns
- cluster the playing tables
- reindex the playing tables
- analyze the playing tables

and it helped now. I'm at ~50ms which satisfies me completely.

If no hints - some debug for explain would be great to be able to
track what's wrong for such a lame developers like me ;)
The problem is solved but I can not tell that I understand why it was
wrong before - and why it's OK now :(

> wstrzalka <wstrzalka(at)gmail(dot)com> writes:
>> Prior to the playing with statistics target (it was 100 by default) I
>> was able to go with the time to 30ms by adding to the query such a
>> condition:

> So what sort of "playing" did you do? It looks to me like the core of
> the problem is the sucky join size estimate here:

>> -> Hash Join (cost=101.53..15650.39 rows=95249 width=8) (actual
>> time=1102.977..1342.675 rows=152 loops=1)
>> Hash Cond: (mal.message_id = m.messageid)

> If it were correctly estimating that only a few message_address_link
> rows would join to each messages row, it'd probably do the right thing.
> But it seems to think there will be thousands of joins for each one...

> regards, tom lane

--
Pozdrowienia,
Wojciech Strzałka