Have query which generate OpenERP ORM. Table have 100k rows.
SELECT "tbl".id
FROM "tbl"
WHERE (("tbl"."active" = 'True') AND ("tbl"."is_company" IS NULL or "tbl"."is_company" = false ))
ORDER BY "tbl"."display_name"
With indexes :
"ix_tbl_pkey" PRIMARY KEY, btree (id)
"ix_active" btree (active)
"ix_displayname" btree (display_name)
"ix_iscompany" btree (is_company)
Query with order by takes 57735.775 ms.
Plan is :
Sort (cost=13031.73..13269.13 rows=94960 width=47) (actual time=57711.753..57725.079 rows=94967 loops=1)
Sort Key: display_name
Sort Method: quicksort Memory: 12918kB
-> Seq Scan on tbl (cost=0.00..5180.90 rows=94960 width=47) (actual time=0.009..57.056 rows=94967 loops=1)
Filter: (active AND ((is_company IS NULL) OR (NOT is_company)))
Rows Removed by Filter: 623
Total runtime: 57735.775 ms
(7 rows)
When i try without order by it takes 65.969 ms.
Plan is :
Seq Scan on tbl (cost=0.00..5180.90 rows=94960 width=4) (actual time=0.026..60.782 rows=94967 loops=1)
Filter: (active AND ((is_company IS NULL) OR (NOT is_company)))
Rows Removed by Filter: 623
Total runtime: 65.969 ms
(4 rows)
With
set enable_sort = off;
it takes 1206.157 ms plan is :
Index Scan using ix_displayname on tbl(cost=0.00..21479.14 rows=94960 width=47) (actual time=29.912..1194.954 rows=94967 loops=1)
Filter: (active AND ((is_company IS NULL) OR (NOT is_company)))
Rows Removed by Filter: 623
Total runtime: 1206.157 ms
(4 rows)
Any way to optimize it with indexes ? Because we cant change something in ORM .
Asked by GeoVIP
(263 rep)
May 19, 2016, 07:48 AM
Last activity: Apr 29, 2025, 06:42 AM
Last activity: Apr 29, 2025, 06:42 AM