In addition to below mail, we have used btree indexes for primary key
columns. Below is the query:
select distinct shipmentre0_.FIN_IDas FIN1_53_0_,
workflowst10_.FIN_ID as FIN1_57_1_,
carriers3_.FIN_ID as FIN1_40_2_,
shipment
We have a query that takes 1min to execute in postgres 10.6 and the same
executes in 4 sec in Oracle database. The query is doing 'select distinct'.
If I add a 'group by' clause, performance in postgres improves
significantly and fetches results in 2 sec (better than oracle). But
unfortunately, we
On Mon, Sep 9, 2019 at 10:38 AM yash mehta wrote:
> In addition to below mail, we have used btree indexes for primary key
> columns. Below is the query:
>
> select distinct shipmentre0_.FIN_IDas FIN1_53_0_,
> workflowst10_.FIN_ID as FIN1_57_1_,
> car
Hi Flo,
PFB the explain plan:
"Limit (cost=5925.59..5944.03 rows=25 width=6994) (actual
time=57997.219..58002.451 rows=25 loops=1)"
" -> Unique (cost=5925.59..5969.10 rows=59 width=6994) (actual
time=57997.218..58002.416 rows=25 loops=1)"
"-> Sort (cost=5925.59..5925.74 rows=59
On Mon, Sep 09, 2019 at 02:00:01PM +0530, yash mehta wrote:
> We have a query that takes 1min to execute in postgres 10.6 and the same
> executes in 4 sec in Oracle database. The query is doing 'select distinct'.
> If I add a 'group by' clause, performance in postgres improves
> significantly and f
There are few things to consider:
- you don't need to use distinct on all columns (and therefore sort all
columns)
- you should try to sort in memory, better than on-disk
- it seems that the planner doesn't predict the good number of rows
Regards,
Florian
On Mon, Sep 9, 2019 at 12:46 PM Justin Pr
If you can't modify the query, then there is nothing more to be done to
optimize the execution afaik. Distinct is much slower than group by in
scenarios like this with many columns. You already identified the disk sort
and increased work mem to get it faster by 3x. There are not any other
tricks of
Hi Michael/Justin/Flo,
Thank you all for your assistance. As Michael said, looks like there are no
more tricks left.
On Mon, Sep 9, 2019 at 9:09 PM Michael Lewis wrote:
> If you can't modify the query, then there is nothing more to be done to
> optimize the execution afaik. Distinct is much slo
