[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: How to improve sql query to achieve the better plan
From: Pavel Stehule <pavel.stehule () gmail ! com>
Date: 2018-09-30 16:55:20
Message-ID: CAFj8pRB=7YpGLNQ0m3skhKjvq7rP4NOY0Lwkv3s07qBF_CY1KQ () mail ! gmail ! com
[Download RAW message or body]
ne 30. 9. 2018 v 18:49 odesílatel Arup Rakshit <ar@zeit.io> napsal:
> I just added it as you said, but I am getting same plan.
>
>
> Sort (cost=62842.16..62846.91 rows=1897 width=35) (actual
> time=1845.831..1845.950 rows=1229 loops=1)
> Sort Key: projects.id
> Sort Method: quicksort Memory: 145kB
> -> HashAggregate (cost=62710.42..62738.88 rows=1897 width=35) (actual
> time=1844.178..1845.060 rows=1229 loops=1)
> Group Key: projects.id
> -> Hash Right Join (cost=159.68..45382.09 rows=364807 width=35)
> (actual time=1.534..618.717 rows=365784 loops=1)
> Hash Cond: (workitems.project_id = projects.id)
> Filter: (workitems.deleted_at IS NULL)
> Rows Removed by Filter: 257457
> -> Seq Scan on workitems (cost=0.00..36653.75 rows=623175
> width=43) (actual time=0.047..213.842 rows=623175 loops=1)
> -> Hash (cost=135.97..135.97 rows=1897 width=16) (actual
> time=1.478..1.478 rows=1897 loops=1)
> Buckets: 2048 Batches: 1 Memory Usage: 105kB
> -> Seq Scan on projects (cost=0.00..135.97 rows=1897
> width=16) (actual time=0.006..0.914 rows=1897 loops=1)
> Planning time: 0.498 ms
> Execution time: 1846.100 ms
>
>
Then there is not too much what can be done better - maybe you can try
PostgreSQL 11 with paralel hash join -- it is process about 6M rows, the
time about 2 sec is good
> ——————
>
> Indexes:
> "workitems_pkey" PRIMARY KEY, btree (id)
> "index_workitems_on_company_id" btree (company_id)
> "index_workitems_on_deleted_at" btree (deleted_at)
> "index_workitems_on_parent_workitem_id" btree (parent_workitem_id)
> "index_workitems_on_project_id" btree (project_id)
> "index_workitems_on_standard_workitem_id" btree (standard_workitem_id)
> "index_workitems_on_workitem_category_id" btree (workitem_category_id)
> "patrial_index_workitems_200_1" btree (project_id) WHERE deleted_at IS
> NULL
>
>
> Thanks,
>
> Arup Rakshit
> ar@zeit.io
>
>
>
> On 30-Sep-2018, at 10:15 PM, Pavel Stehule <pavel.stehule@gmail.com>
> wrote:
>
> CREATE INDEX ON workitems(project_id) WHERE deleted_at is null
>
>
>
[Attachment #3 (text/html)]
<div dir="ltr"><br><br><div class="gmail_quote"><div dir="ltr">ne 30. 9. 2018 v \
18:49 odesílatel Arup Rakshit <<a href="mailto:ar@zeit.io">ar@zeit.io</a>> \
napsal:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 \
.8ex;border-left:1px #ccc solid;padding-left:1ex"><div \
style="word-wrap:break-word;line-break:after-white-space"><div>I just added it as you \
said, but I am getting same plan.</div><div><br></div><div><br></div><div><div>Sort \
(cost=62842.16..62846.91 rows=1897 width=35) (actual time=1845.831..1845.950 \
rows=1229 loops=1)</div><div> Sort Key: <a href="http://projects.id" \
target="_blank">projects.id</a></div><div> Sort Method: quicksort Memory: \
145kB</div><div> -> HashAggregate (cost=62710.42..62738.88 rows=1897 \
width=35) (actual time=1844.178..1845.060 rows=1229 loops=1)</div><div> \
Group Key: <a href="http://projects.id" target="_blank">projects.id</a></div><div> \
-> Hash Right Join (cost=159.68..45382.09 rows=364807 width=35) (actual \
time=1.534..618.717 rows=365784 loops=1)</div><div> Hash Cond: \
(workitems.project_id = <a href="http://projects.id" \
target="_blank">projects.id</a>)</div><div> Filter: \
(workitems.deleted_at IS NULL)</div><div> Rows Removed by Filter: \
257457</div><div> -> Seq Scan on workitems \
(cost=0.00..36653.75 rows=623175 width=43) (actual time=0.047..213.842 rows=623175 \
loops=1)</div><div> -> Hash (cost=135.97..135.97 rows=1897 \
width=16) (actual time=1.478..1.478 rows=1897 loops=1)</div><div> \
Buckets: 2048 Batches: 1 Memory Usage: 105kB</div><div> \
-> Seq Scan on projects (cost=0.00..135.97 rows=1897 width=16) (actual \
time=0.006..0.914 rows=1897 loops=1)</div><div>Planning time: 0.498 \
ms</div><div>Execution time: 1846.100 \
ms</div></div><div><br></div></div></blockquote><div><br></div><div>Then there is not \
too much what can be done better - maybe you can try PostgreSQL 11 with paralel hash \
join -- it is process about 6M rows, the time about 2 sec is good<br></div><div> \
</div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div \
style="word-wrap:break-word;line-break:after-white-space"><div></div><div>——————</div><div><br></div><div><div \
style="margin:0px;font-stretch:normal;font-size:11px;line-height:normal;font-family:Menlo;background-color:rgb(255,255,255)"><span \
style="font-variant-ligatures:no-common-ligatures">Indexes:</span></div><div \
style="margin:0px;font-stretch:normal;font-size:11px;line-height:normal;font-family:Menlo;background-color:rgb(255,255,255)"><span \
style="font-variant-ligatures:no-common-ligatures"> "workitems_pkey" \
PRIMARY KEY, btree (id)</span></div><div \
style="margin:0px;font-stretch:normal;font-size:11px;line-height:normal;font-family:Menlo;background-color:rgb(255,255,255)"><span \
style="font-variant-ligatures:no-common-ligatures"> \
"index_workitems_on_company_id" btree (company_id)</span></div><div \
style="margin:0px;font-stretch:normal;font-size:11px;line-height:normal;font-family:Menlo;background-color:rgb(255,255,255)"><span \
style="font-variant-ligatures:no-common-ligatures"> \
"index_workitems_on_deleted_at" btree (deleted_at)</span></div><div \
style="margin:0px;font-stretch:normal;font-size:11px;line-height:normal;font-family:Menlo;background-color:rgb(255,255,255)"><span \
style="font-variant-ligatures:no-common-ligatures"> \
"index_workitems_on_parent_workitem_id" btree \
(parent_workitem_id)</span></div><div \
style="margin:0px;font-stretch:normal;font-size:11px;line-height:normal;font-family:Menlo;background-color:rgb(255,255,255)"><span \
style="font-variant-ligatures:no-common-ligatures"> \
"index_workitems_on_project_id" btree (project_id)</span></div><div \
style="margin:0px;font-stretch:normal;font-size:11px;line-height:normal;font-family:Menlo;background-color:rgb(255,255,255)"><span \
style="font-variant-ligatures:no-common-ligatures"> \
"index_workitems_on_standard_workitem_id" btree \
(standard_workitem_id)</span></div><div \
style="margin:0px;font-stretch:normal;font-size:11px;line-height:normal;font-family:Menlo;background-color:rgb(255,255,255)"><span \
style="font-variant-ligatures:no-common-ligatures"> \
"index_workitems_on_workitem_category_id" btree \
(workitem_category_id)</span></div><div \
style="margin:0px;font-stretch:normal;font-size:11px;line-height:normal;font-family:Menlo;background-color:rgb(255,255,255)"><span \
style="font-variant-ligatures:no-common-ligatures"> \
"patrial_index_workitems_200_1" btree (project_id) WHERE deleted_at IS \
NULL</span></div></div><div><br></div><br><div> <div \
style="color:rgb(0,0,0);font-family:Helvetica;font-size:12px;font-style:normal;font-va \
riant-caps:normal;font-weight:normal;letter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px">Thanks,</div><div \
style="color:rgb(0,0,0);font-family:Helvetica;font-size:12px;font-style:normal;font-va \
riant-caps:normal;font-weight:normal;letter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px"><br></div><div \
style="color:rgb(0,0,0);font-family:Helvetica;font-size:12px;font-style:normal;font-va \
riant-caps:normal;font-weight:normal;letter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px">Arup \
Rakshit</div><div style="color:rgb(0,0,0);font-family:Helvetica;font-size:12px;font-st \
yle:normal;font-variant-caps:normal;font-weight:normal;letter-spacing:normal;text-alig \
n:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px"><a \
href="mailto:ar@zeit.io" target="_blank">ar@zeit.io</a></div><div \
style="color:rgb(0,0,0);font-family:Helvetica;font-size:12px;font-style:normal;font-va \
riant-caps:normal;font-weight:normal;letter-spacing:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px"><br></div><br \
class="m_-328237321927644756Apple-interchange-newline"> </div>
<div><br><blockquote type="cite"><div>On 30-Sep-2018, at 10:15 PM, Pavel Stehule \
<<a href="mailto:pavel.stehule@gmail.com" \
target="_blank">pavel.stehule@gmail.com</a>> wrote:</div><br \
class="m_-328237321927644756Apple-interchange-newline"><div><span \
style="font-family:Helvetica;font-size:12px;font-style:normal;font-variant-caps:normal \
;font-weight:normal;letter-spacing:normal;text-align:start;text-indent:0px;text-transf \
orm:none;white-space:normal;word-spacing:0px;text-decoration:none;float:none;display:inline!important">CREATE \
INDEX ON<span class="m_-328237321927644756Apple-converted-space"> \
</span></span><span class="m_-328237321927644756gmail-m_-2430484406619492684Apple-tab-span" \
style="font-family:Helvetica;font-size:12px;font-style:normal;font-variant-caps:normal \
;font-weight:normal;letter-spacing:normal;text-align:start;text-indent:0px;text-transf \
orm:none;word-spacing:0px;text-decoration:none;white-space:pre-wrap"></span><span \
style="font-family:Helvetica;font-size:12px;font-style:normal;font-variant-caps:normal \
;font-weight:normal;letter-spacing:normal;text-align:start;text-indent:0px;text-transf \
orm:none;white-space:normal;word-spacing:0px;text-decoration:none;float:none;display:inline!important">workitems(project_id) \
WHERE deleted_at is null</span></div></blockquote></div><br></div></blockquote></div></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic