Sample Header Ad - 728x90

How to optimize query with order by

0 votes
1 answer
313 views
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