Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-51), 64-bit

Dedicated DB server

  • 4GB ram
  • Shared_Buffers = 1 GB
  • Effective_cache_size = 3GB
  • Work_mem = 32MB

Analyze done

Queries ran multiple times, same differences/results

Default Statistics = 1000

Query (5366ms) :

explain analyze
select
    initcap (fullname)
  , initcap(issuer)
  , upper(rsymbol)
  , initcap(industry)
  , activity
  , to_char(shareschange,'FM9,999,999,999,999,999')
  , sharespchange || + E'\%'
from changes
where activity in (4,5) and mfiled >= (select max(mfiled) from changes)
order by shareschange asc
limit 15

Slow Ascending explain Analyze:

http://explain.depesz.com/s/zFz

Query (15ms) :

explain analyze
select
    initcap (fullname)
  , initcap(issuer)
  , upper(rsymbol)
  , initcap(industry)
  , activity
  , to_char(shareschange,'FM9,999,999,999,999,999')
  , sharespchange ||+ E'\%'
from changes
where activity in (4,5) and mfiled >= (select max(mfiled) from changes)
order by shareschange desc limit 15

Fast descending explain analyze:

http://explain.depesz.com/s/OP7

The index: changes_shareschange is a btree index created with default ascending order. The is index size is 32mb

The query plan and estimates are exactly the same, except desc has index scan backwards instead of index scan for changes_shareschange.

Yet, actual runtime performance is different by 357x slower for the ascending version instead of descending.

Why and how do I fix it?

share|improve this question

migrated from stackoverflow.com Feb 8 '12 at 19:05

This question came from our site for professional and enthusiast programmers.

    
Did you try to create a 'desc' index? (create index ... on ... desc) –  ondra Feb 8 '12 at 16:59
    
@ondra: Generally, scanning a b-tree index is equally fast in either direction. –  Erwin Brandstetter Feb 8 '12 at 19:00
    
To the OP: could you add the table definition (including indexes) to the post? TIA. –  wildplasser Feb 8 '12 at 19:04
    
@wildplasser: I can not share the entire table and index, unless you know of an anonymizer site to obscure names? It is a large table 55+ rows, 1.6GB size. With all indexes, size if 5.6GB. Being used for reporting/data warehouse. Here is the definition for column and index: shareschange | numeric | "changes_shareschange" btree (shareschange) –  user1197681 Feb 8 '12 at 19:16
    
Do you have NULLs in the shareschange column? If yes (and lots of them), then use a partial index on the non-NULL elements only. –  j.p. Feb 10 '12 at 13:22

2 Answers 2

Since I like replacing aggregate functions by old-fashioned self-joins and NOT EXISTS clauses, here is my attempt:

SET search_path='tmp';

DROP TABLE tmp.changes CASCADE;
CREATE TABLE tmp.changes
        ( id integer NOT NULL PRIMARY KEY
        , fullname varchar
        , issuer varchar
        , rsymbol varchar
        , industry varchar
        , activity INTEGER NOT NULL
        , shareschange FLOAT
        , sharespchange FLOAT
        , mfiled FLOAT
        );

        -- lacking information from the OP
        -- I can only presume a flat distribution.
INSERT INTO tmp.changes(id, activity, shareschange,sharespchange,mfiled )
SELECT nm.*
        , (random() *20)::integer -- mfiled
        , random() *10000
        , random() *100
        , random() *100000
FROM generate_series(1,1000000) nm
        ;

ALTER TABLE tmp.changes
        ALTER shareschange
        SET STATISTICS 1000
        ;
ALTER TABLE tmp.changes
        ALTER mfiled
        SET STATISTICS 1000
        ;

VACUUM ANALYZE tmp.changes
        ;


CREATE INDEX changes_mfiled_shareschange
    ON tmp.changes(mfiled,shareschange)
        ;

EXPLAIN ANALYZE
SELECT initcap(ch.fullname) AS some_name1
     , initcap(ch.issuer) AS some_name2
     , upper(ch.rsymbol) AS some_name3
     , initcap(ch.industry) AS some_name4
     , ch.activity
     , to_char(ch.shareschange,'FM9,999,999,999,999,999') AS some_name5
     , ch.sharespchange || '%' AS some_name6
FROM   changes ch
WHERE  ch.activity IN (4,5)
        -- NOTE: the subquery is *not* correlated.
        -- [I had expected a subselect of nx.activity IN (4,5)
        -- like in the main query. ]
AND    NOT EXISTS (SELECT * FROM changes nx
        WHERE nx.mfiled > ch.mfiled
        )
ORDER  BY ch.shareschange ASC
LIMIT  15
        ;
share|improve this answer
  • What kind of operator is this: ||+? If that is just a typo and you want to concatenate ||, then simplify to:

    sharespchange || '%'
    
  • Why ..

    mfiled >= (SELECT max(mfiled) FROM changes)
    

    And not:

    mfiled = (SELECT max(mfiled) FROM changes)
    

    (There cannot be a bigger mfiled anyway.)

So I arrive at this query:

EXPLAIN ANALYZE
SELECT initcap(fullname) AS some_name1
     , initcap(issuer) AS some_name2
     , upper(rsymbol) AS some_name3
     , initcap(industry) AS some_name4
     , activity
     , to_char(shareschange,'FM9,999,999,999,999,999') AS some_name5
     , sharespchange || '%' AS some_name6
FROM   changes
WHERE  activity IN (4,5)
AND    mfiled = (SELECT max(mfiled) FROM changes c)
ORDER  BY shareschange ASC
LIMIT  15

The query plan shows that the expensive part is the LIMIT. In the fast variant, the index changes_shareschange is scanned backward twice (first time for the max()). Also the first 15 rows found in the index can be returned (walking it in matching direction) and do not need to be re-sorted. That is the perfect case.

In the slow variant, the index is scanned in both directions. That is most likely more expensive. In addition, the server cannot just return the first 15 rows found, but must retrieve all matching rows, re-sort and then return the top 15 rows.

Does using = instead of >= like I describe above change anything?

Generally, scanning a b-tree index is equally fast in either direction.

I assume your index changes_shareschange is a plain btree index like:

CREATE INDEX changes_shareschange ON changes(shareschange);
share|improve this answer
2  
||+ is a customer operator, same as || except it doesn't die with nulls –  user1197681 Feb 8 '12 at 18:58
    
@user1197681: I see. You can still simplify E'\%' to '%'. Also, you may be interested in the concat_ws() function, new in Postgres 9.1, similar to the one in MySQL. –  Erwin Brandstetter Feb 8 '12 at 19:03
    
This query is "built" and then processed. The mfiled >= versus = is to be open to different right hand section (like a standard date). As mentioned, the index is b-tree, created like shown. I don't think it scans changes_shareschanges twice - just once. The max is used for mfiled - with it's own index. I tried changed it to = for mfiled like above - but no change. –  user1197681 Feb 8 '12 at 19:10
    
You are right, I confused the index names. I can't explain the big discrepancy then. –  Erwin Brandstetter Mar 9 '12 at 0:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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