Vryjdfkk

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 .

Using an index consisting of the columns display_name, id, active and is_company would enable the query to be run using the index alone. Ordering the columns in the order provided would remove the sort requirement. This comes at a slightly update cost, and could replace the ix_displayname index.

If active were rare, it would be faster to order the columns active, display_name, id, 'is_company. However, updating the active column would have higher update costs.

You may need to select the display_name in an inner query, or use an index hint.