Hi!!
what happens if you change your query to:
SELECT r.id, r.age, r.gender, r.created, a.adverse, d.drug
FROM reports r
INNER JOIN report_drugs d USING (rid)
INNER JOIN report_adverses a USING (rid)
WHERE a.adverse = ANY (ARRAY['back pain - nonspecific', 'nonspecific back
pain', 'back
On Thu, 28 Apr 2016 16:38:53 +0200
Alban Hertroys wrote:
Hi Alban
First off ... thanks you for your time on this so far and giving me the
educational smacks on the head :) I appreciate this a lot.
> You're doing ~9 times as many index lookups. A slowdown of 6x of this
> part of the query seem
On 28 April 2016 at 08:36, Tim van der Linden wrote:
> On Wed, 27 Apr 2016 13:48:06 +0200
> Alban Hertroys wrote:
>
>> In this case, you're using the values in adverse to filter relevant rid's
>> for the FK join, so you might be better off with the inverse of above index:
>> create index o
On Wed, 27 Apr 2016 13:48:06 +0200
Alban Hertroys wrote:
> In this case, you're using the values in adverse to filter relevant rid's for
> the FK join, so you might be better off with the inverse of above index:
> create index on report_adverses (adverse, rid);
> create index on repo
On Apr 27, Tim van der Linden modulated:
...
> I'm joining three fairly large tables together, and it is slow. The tables
> are:
>
> - "reports": 6 million rows
> - "report_drugs": 20 million rows
> - "report_adverses": 20 million rows
>
...
> All tables have indexes on the "id"/"rid" columns an
On Wed, 27 Apr 2016 13:48:06 +0200
Alban Hertroys wrote:
Hi Alban
Thanks for chiming in!
> Since you're not using age and gender in this (particular) query until the
> rows are combined into a result set already, it doesn't make a whole lot of
> sense to add them to the index. Moreover, since
> On 27 Apr 2016, at 4:09, David Rowley wrote:
>
> On 27 April 2016 at 11:27, Tim van der Linden wrote:
>> The query:
>>
>> SELECT r.id, r.age, r.gender, r.created, a.adverse, d.drug
>> FROM reports r
>> JOIN report_drugs d ON d.rid = r.id
>> JOIN report_adverses a ON a.rid = r.id
>> WHERE a.
On Wed, 27 Apr 2016 22:40:43 +1200
David Rowley wrote:
Hi David
> > ...
> > Planning time: 15.968 ms
> > Execution time: 4313.755 ms
> >
> > Both the (rid, adverse) and the (id, age, gender, created) indexes are now
> > used.
> >
>
> Seems the (rid, adverse) is not being used. report_adverse
On 27 April 2016 at 22:29, Tim van der Linden wrote:
> Sort (cost=372968.28..372969.07 rows=317 width=41) (actual
> time=9308.174..9308.187 rows=448 loops=1)
>Sort Key: r.created
> Sort (cost=66065.73..66066.59 rows=344 width=41) (actual
> time=4313.679..4313.708 rows=448 loops=1)
>S
On Wed, 27 Apr 2016 09:14:27 +0300
Victor Yegorov wrote:
Hi Victor
> > ...
>
> Can you post output of `EXPLAIN (analyze, buffers)`, please?
> It'd be good to check how many buffers are hit/read during Index Scans.
Happy to, here it is:
Sort (cost=107727.85..107728.71 rows=344 width=41) (act
On Wed, 27 Apr 2016 14:09:04 +1200
David Rowley wrote:
Hi David
Thanks for your time on this. I tried your proposals with the results below.
> > ...
> > Under 5 ms. The same goes for querying the "adverse" column in the
> > "report_adverses" table: under 20 ms.
>
> I'm not sure why you're com
On Wed, 27 Apr 2016 01:45:55 +
Sameer Kumar wrote:
Hi Sameer
Thanks for taking the time to look into this!
> > ...
> Quite clearly the nested loop joins are the most costly operations here.
Indeed.
> > ...
> I suppose. It might help if the filters are performed before the join. I am
> n
2016-04-27 2:27 GMT+03:00 Tim van der Linden :
> The plan:
>
> Sort (cost=105773.63..105774.46 rows=333 width=76) (actual
> time=5143.162..5143.185 rows=448 loops=1)
>Sort Key: r.created
>Sort Method: quicksort Memory: 60kB
>-> Nested Loop (cost=1.31..105759.68 rows=333 width=76) (
On 27 April 2016 at 11:27, Tim van der Linden wrote:
> Hi all
>
> I have asked this question in a somewhat different form on the DBA
> Stackexchange site, but without much luck
> (https://dba.stackexchange.com/questions/136423/postgresql-slow-join-on-three-large-tables).
> So I apologize for po
On Wed, 27 Apr 2016 07:28 Tim van der Linden, wrote:
> Hi all
>
> I have asked this question in a somewhat different form on the DBA
> Stackexchange site, but without much luck (
> https://dba.stackexchange.com/questions/136423/postgresql-slow-join-on-three-large-tables).
> So I apologize for pos
Hi all
I have asked this question in a somewhat different form on the DBA
Stackexchange site, but without much luck
(https://dba.stackexchange.com/questions/136423/postgresql-slow-join-on-three-large-tables).
So I apologize for possible double posting, but I hope this might get a better
respon
16 matches
Mail list logo
