Re: How to improve sql query to achieve the better plan

Поиск
Список
Период
Сортировка
От Arup Rakshit
Тема Re: How to improve sql query to achieve the better plan
Дата
Msg-id 96DF7B40-A783-47F5-A7EA-BE6F022456AF@zeit.io
обсуждение исходный текст
Ответ на Re: How to improve sql query to achieve the better plan  (Pavel Stehule <pavel.stehule@gmail.com>)
Ответы Re: How to improve sql query to achieve the better plan
Список pgsql-general
Дерево обсуждения
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

——————

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



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

В списке pgsql-general по дате отправления: