From: | Durgamahesh Manne <maheshpostgres9(at)gmail(dot)com> |
---|---|
To: | andreas(at)a-kretschmer(dot)de |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: *Regarding brin_index on required column of the table |
Date: | 2018-09-20 09:58:46 |
Message-ID: | CAJCZkoL6zBga0VMDD-rRmXX+YUTiGduSPShQQFkwZWewwRjyNw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Sep 20, 2018 at 3:22 PM Andreas Kretschmer <andreas(at)a-kretschmer(dot)de>
wrote:
> Hi,
>
>
> the problem is there:
>
>
> Am 20.09.2018 um 11:48 schrieb Durgamahesh Manne:
> > Unique (cost=5871873.64..6580630.73 rows=7400074 width=89) (actual
> > time=326397.551..389515.863 rows=3700000 loops=1)
> > |
> > | -> Sort (cost=5871873.64..5973124.65 rows=40500405 width=89)
> > (actual time=326397.550..372470.846 rows=40500000 loops=1)
> > |
> > | Sort Key: sub_head."vchSubmittersCode", rec."vchFileName",
> > rec."vchCusipFundIDSubFundID", rec."vchFundUnitPrice",
> > sub_head."vchValuationDate", rec."vchAdvisorLabel" |
> > | Sort Method: external merge Disk: 3923224kB
> > |
>
> Please check the execution time without DISTINCT.
>
> Regards, Andreas
> --
>
> 2ndQuadrant - The PostgreSQL Support Company.
> www.2ndQuadrant.com
>
>
hi
as per your request
i ran explain analyze query without distinct
+------------------------------------------------------------------------------------------------------------------------------------------------------+
|
QUERY PLAN
|
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Hash Join (cost=21.06..457723.28 rows=40500405 width=89) (actual
time=0.429..6763.942 rows=40500000 loops=1)
|
| Hash Cond: ((rec."vchSubmittersCode")::text =
(sub_head."vchSubmittersCode")::text)
|
| -> Seq Scan on table1 rec (cost=0.00..1822.66 rows=100001 width=80)
(actual time=0.006..48.610 rows=100000 loops=1) |
| Filter: (NOT "bFetch")
|
| Rows Removed by Filter: 4706
|
| -> Hash (cost=16.00..16.00 rows=405 width=11) (actual
time=0.404..0.404 rows=405 loops=1)
|
| Buckets: 1024 Batches: 1 Memory Usage: 26kB
|
| -> Seq Scan on table2 sub_head (cost=0.00..16.00 rows=405
width=11) (actual time=0.004..0.326 rows=405 loops=1) |
| Filter: (NOT "bFetch")
|
| Rows Removed by Filter: 375
|
| Planning time: 0.351 ms
|
| Execution time: 8371.819 ms
|
+------------------------------------------------------------------------------------------------------------------------------------------------------+
(12 rows)
From | Date | Subject | |
---|---|---|---|
Next Message | Durgamahesh Manne | 2018-09-20 10:11:46 | Re: *Regarding brin_index on required column of the table |
Previous Message | Andreas Kretschmer | 2018-09-20 09:52:55 | Re: *Regarding brin_index on required column of the table |