From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Adding LEFT JOIN to a query has increased execution time 10 times |
Date: | 2019-01-07 13:56:47 |
Message-ID: | CAADeyWjw2UASz-FM3UZxsViTTeLVU4RJVqRJFP+8pdOWTdK2-g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi Andrew -
On Mon, Jan 7, 2019 at 12:00 AM Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
wrote:
> >>>>> "Alexander" == Alexander Farber <alexander(dot)farber(at)gmail(dot)com> writes:
> Alexander> With the further help of the IRC folks the query has been
> Alexander> optimized (7-10 seconds -> 0.3 second)
>
> 0.3 MILLIseconds, actually.
>
> (You chanced not to catch me around on IRC, but I see that didn't
> matter.)
>
it is amazing to see how the query I got used to be so slow in the past
months is suddenly returning so quickly.
Yes, I figured I have missed you on the IRC yesterday, but the advice to go
there (haven't used it for 20 years) has been very helpful anyway.
> Alexander> CREATE INDEX ON words_moves (gid, played DESC);
> Alexander> CREATE INDEX ON words_social (uid, stamp DESC);
>
> I'm not a big fan of using DESC on indexes; it's almost never needed,
> because any btree index can be scanned in reverse. (You only actually
> need it if you're mixing ASC/DESC orderings in an ORDER BY and want an
> index that matches it.)
>
Ok good to know, I will remove the DESC from the indices.
Regards
Alex
From | Date | Subject | |
---|---|---|---|
Next Message | Stéphane KANSCHINE | 2019-01-07 14:36:21 | Re: Multiple postgresql clusters with same version and separate binaries |
Previous Message | Alexander Farber | 2019-01-07 13:50:23 | Not sure which part of the query needs optimization |