0

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 .

  • Something is really wrong in that sort. What sort of hardware are you on? Also, what are you doing with 90+k rows? Could you please include the table definition, too? – dezso May 19 '16 at 8:24
  • @dezso ORM use this query for sorting data. I just check slow querys and one of them is this. Field display_name contains name and surnames – GeoVIP May 19 '16 at 8:33
  • I'm simulating a similar scenario in one of my own tables with 100k rows of real data, and get the same results: fast with enable_sort = off, slow with on. – Ziggy Crueltyfree Zeitgeister May 19 '16 at 8:41
  • @ZiggyCrueltyfreeZeitgeister and did you find some solution ? – GeoVIP May 19 '16 at 10:45
  • @GeoVIP no, but I didn't know about enable_sort before, and I'm sure it will come in handy for some of my own queries! It looks like that is the solution, at least one that would be satisfactory to me. – Ziggy Crueltyfree Zeitgeister May 19 '16 at 22:25
0

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.

  • An index hint? The question is tagged postgres. – ypercubeᵀᴹ Mar 20 at 7:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.