Sfrgttk

Is there a distance limit for minecart tracks?

Why didn’t Eve recognize the little cockroach as a living organism?

categorizing a variable turns it from insignificant to significant

I keep switching characters, how do I stop?

Make a Bowl of Alphabet Soup

Checking @@ROWCOUNT failing

Can a Knock spell open the door to Mordenkainen's Magnificent Mansion?

Has the laser at Magurele, Romania reached a tenth of the Sun's power?

Do native speakers use "ultima" and "proxima" frequently in spoken English?

Is there a POSIX way to shutdown a UNIX machine?

How do you say "Trust your struggle." in French?

Weird lines in Microsoft Word

Should I warn a new PhD Student?

Why does a 97 / 92 key piano exist by Bosendorfer?

Put the phone down / Put down the phone

What is the meaning of "You've never met a graph you didn't like?"

Center page as a whole without centering each element individually

Why doesn't Gödel's incompleteness theorem apply to false statements?

Reason why a kingside attack is not justified

Amorphous proper classes in MK

Not hide and seek

Connection Between Knot Theory and Number Theory

Is this saw blade faulty?

How would a solely written language work mechanically

How to optimize query with order by

PostgreSQL 9.2 (PostGIS) performance problemHow can I speed up a Postgres query containing lots of Joins with an ILIKE conditionpostgres explain plan with giant gaps between operationsHow to optimize multiple ORDER BYs?Slow fulltext search due to wildly inaccurate row estimatesIndex for numeric field is not usedpostgresql 9.2 hash join issueWhy does PostgreSQL perform a seq scan when comparing a numeric value with a bigint column?Sorting killing my postgresql queryWhy is this query with WHERE, ORDER BY and LIMIT so slow?

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 .

postgresql postgresql-9.2

share|improve this question

edited May 19 '16 at 14:09

GeoVIP

asked May 19 '16 at 7:48

GeoVIPGeoVIP

183212

bumped to the homepage by Community♦ 7 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

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 .

postgresql postgresql-9.2

share|improve this question

edited May 19 '16 at 14:09

GeoVIP

asked May 19 '16 at 7:48

GeoVIPGeoVIP

183212

bumped to the homepage by Community♦ 7 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

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 .

postgresql postgresql-9.2

share|improve this question

edited May 19 '16 at 14:09

GeoVIP

asked May 19 '16 at 7:48

GeoVIPGeoVIP

183212

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" 

    "ix_tbl_pkey" PRIMARY KEY, btree (id)

    "ix_active" btree (active)

    "ix_displayname" btree (display_name)

    "ix_iscompany" btree (is_company)

 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)

 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)

 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)

share|improve this question

edited May 19 '16 at 14:09

GeoVIP

asked May 19 '16 at 7:48

GeoVIPGeoVIP

183212

share|improve this question

edited May 19 '16 at 14:09

GeoVIP

asked May 19 '16 at 7:48

GeoVIPGeoVIP

183212

asked May 19 '16 at 7:48

GeoVIPGeoVIP

183212

asked May 19 '16 at 7:48

GeoVIPGeoVIP

183212

1 Answer
1

active

oldest

votes

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.

share|improve this answer

answered May 20 '16 at 1:16

BillThorBillThor

4,2681412

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f138977%2fhow-to-optimize-query-with-order-by%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

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.

share|improve this answer

answered May 20 '16 at 1:16

BillThorBillThor

4,2681412

add a comment | 

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.

share|improve this answer

answered May 20 '16 at 1:16

BillThorBillThor

4,2681412

add a comment | 

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.

share|improve this answer

answered May 20 '16 at 1:16

BillThorBillThor

4,2681412

share|improve this answer

answered May 20 '16 at 1:16

BillThorBillThor

4,2681412

answered May 20 '16 at 1:16

BillThorBillThor

4,2681412

answered May 20 '16 at 1:16

BillThorBillThor

4,2681412