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.

Do you have any suggestion on how to improve a table structure or its indexes in order to have a single query run faster? The table has this structure:

    docid          | character varying(17) | not null
    seqnr          | numeric               | not null default 1
    creatinguser   | character varying(50) | not null
    creatingdate   | date                  | 
    lastchangeuser | character varying(50) | not null
    lastchangedate | date                  | 
    description    | character varying(50) | 
Indexes:
    "systemdatabydocument_pkey" PRIMARY KEY, btree (docid, seqnr)
    "sdbdchanged" btree (lastchangedate)
    "sdbdcreated" btree (creatingdate)

my query (please note that the value in WHERE changes) is:

SELECT MAX(creatingDate), MAX(lastChangeDate)
FROM agenzia.SystemDataByDocument
WHERE docId = 'Sedi';

and explain analyze on a old/slow system with about 250k records on postgresql 8.3.17 is:

 Result  (cost=99.05..99.06 rows=1 width=0) (actual time=573.767..573.768 rows=1 loops=1)
   InitPlan
     ->  Limit  (cost=0.00..39.81 rows=1 width=8) (actual time=280.417..280.418 rows=1 loops=1)
       ->  Index Scan Backward using sdbdcreated on systemdatabydocument  (cost=0.00..22887.95 rows=575 width=8) (actual time=280.414..280.414 rows=1 loops=1)
             Filter: ((creatingdate IS NOT NULL) AND ((docid)::text = 'Sedi'::text))
     ->  Limit  (cost=0.00..59.24 rows=1 width=8) (actual time=293.332..293.333 rows=1 loops=1)
       ->  Index Scan Backward using sdbdchanged on systemdatabydocument  (cost=0.00..34064.51 rows=575 width=8) (actual time=293.328..293.328 rows=1 loops=1)
             Filter: ((lastchangedate IS NOT NULL) AND ((docid)::text = 'Sedi'::text))
 Total runtime: 573.837 ms

while the same plan on postgresql 9.1.11 (on a new/faster machine with about 500k records) is almost the same:

 Result  (cost=117.40..117.41 rows=1 width=0) (actual time=537.812..537.813 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..59.73 rows=1 width=4) (actual time=279.532..279.532 rows=0 loops=1)
       ->  Index Scan Backward using sdbdcreated on systemdatabydocument  (cost=0.00..30342.46 rows=508 width=4) (actual time=279.525..279.525 rows=0 loops=1)
             Index Cond: (creatingdate IS NOT NULL)
             Filter: ((docid)::text = 'Sedi'::text)
   InitPlan 2 (returns $1)
     ->  Limit  (cost=0.00..57.67 rows=1 width=4) (actual time=258.270..258.270 rows=0 loops=1)
       ->  Index Scan Backward using sdbdchanged on systemdatabydocument  (cost=0.00..29296.76 rows=508 width=4) (actual time=258.267..258.267 rows=0 loops=1)
             Index Cond: (lastchangedate IS NOT NULL)
             Filter: ((docid)::text = 'Sedi'::text)
 Total runtime: 537.924 ms

Thank you very much.

Addendum 1: adding an index with all fields does not help:

=>create index testtesttest3 on agenzia.systemdatabydocument (docid,creatingDate,lastChangeDate);
CREATE INDEX
Tempo: 2180,780 ms
=> explain analyze SELECT MAX(creatingDate), MAX(lastChangeDate) FROM agenzia.SystemDataByDocument WHERE docId = 'Sedi';
                                                                        QUERY PLAN                                                                             
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=62.92..62.93 rows=1 width=0) (actual time=281.283..281.284 rows=1 loops=1)
   InitPlan
     ->  Limit  (cost=0.00..3.68 rows=1 width=8) (actual time=0.160..0.161 rows=1 loops=1)
       ->  Index Scan Backward using testtesttest3 on systemdatabydocument  (cost=0.00..2118.04 rows=575 width=8) (actual time=0.157..0.157 rows=1 loops=1)
             Index Cond: ((docid)::text = 'Sedi'::text)
             Filter: (creatingdate IS NOT NULL)
     ->  Limit  (cost=0.00..59.24 rows=1 width=8) (actual time=281.109..281.110 rows=1 loops=1)
       ->  Index Scan Backward using sdbdchanged on systemdatabydocument  (cost=0.00..34063.25 rows=575 width=8) (actual time=281.106..281.106 rows=1 loops=1)
             Filter: ((lastchangedate IS NOT NULL) AND ((docid)::text = 'Sedi'::text))
 Total runtime: 281.365 ms
(10 rows)
share|improve this question
1  
I think a composite index(docid,creatingDate,lastChangeDate) would help –  Mihai Jan 30 at 11:03
    
Why is seqnr a numeric, not an integer? Also, the 50 char length limit seems to be some arbitrary limit which could be completely omitted. Furthermore, I would use some underscores to render column names a bit more readable. I see these do not help with query performance, for that see @Mihai's comment :) –  dezso Jan 30 at 11:26
    
@Mihai, your suggestion does not work since even with that new index, the plan does not change. –  eppesuig Jan 30 at 12:06
    
@dezso, your comment about the table structure is sadly not acceptable since this schema is used across many different DBMS. Let's say this is the schema that works everywhere (informix, oracle, sql-server, mysql, postgresql). About these limits (like 50 chars in length), they are required by the application. –  eppesuig Jan 30 at 12:09
    
But i see in your explain that the time was cut in half. –  Mihai Jan 30 at 12:13
show 1 more comment

2 Answers

As suggested by @Mihai, adding an index that include docid as first column help. The best solution is to create two of them, one for (docid,creatingDate) and the second one for (docid,lastChangeDate). Please note for simplyfing the index scan, the second fild is ordered DESCENDING, infact the first two indexes are ignored. The new plan is now:

=# create index testtesttest1 on agenzia.systemdatabydocument (docid,creatingDate);
CREATE INDEX
=# create index testtesttest2 on agenzia.systemdatabydocument (docid,lastChangeDate);
CREATE INDEX
=# create index testtesttest3 on agenzia.systemdatabydocument (docid,creatingDate DESC);
CREATE INDEX
=# create index testtesttest4 on agenzia.systemdatabydocument (docid,lastChangeDate DESC);
CREATE INDEX
=# explain analyze SELECT MAX(creatingDate), MAX(lastChangeDate) FROM agenzia.SystemDataByDocument WHERE docId = 'Sedi';
                                                                  QUERY PLAN                                                                       
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=5.40..5.41 rows=1 width=0) (actual time=0.151..0.151 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..2.70 rows=1 width=4) (actual time=0.091..0.091 rows=0 loops=1)
       ->  Index Scan using testtesttest3 on systemdatabydocument  (cost=0.00..1372.58 rows=508 width=4) (actual time=0.085..0.085 rows=0 loops=1)
             Index Cond: (((docid)::text = 'Sedi'::text) AND (creatingdate IS NOT NULL))
   InitPlan 2 (returns $1)
     ->  Limit  (cost=0.00..2.70 rows=1 width=4) (actual time=0.054..0.054 rows=0 loops=1)
       ->  Index Scan using testtesttest4 on systemdatabydocument  (cost=0.00..1372.58 rows=508 width=4) (actual time=0.053..0.053 rows=0 loops=1)
             Index Cond: (((docid)::text = 'Sedi'::text) AND (lastchangedate IS NOT NULL))
 Total runtime: 0.256 ms
share|improve this answer
    
Just curious,could somebody in the know explain why 2 separate indexes (docid,creatingDate),(docid,lastChangeDate) work much better than (docid,creatingDate,lastChangeDate)? –  Mihai Jan 30 at 12:33
2  
@Mihai because that (triple) index would be useful if you wanted to get the MAX(lastChangeDate) for every docid - creatingDate combination or for a specific docid - creatingDate combination. But the query is for a specific docid only. So with the triple index, it has to go through (index scan) all the tree (below the docid='Sedi' value). –  ypercube Jan 30 at 12:46
    
@Mihai, I believe the index is ordered on these colums, so the max(third columns) is not directly accessible in the index and it require a scan. The two different indexes solve the problem –  eppesuig Jan 30 at 12:46
    
I just added the reverse order for second field in index, so that MAX function just get the first record indexed. –  eppesuig Jan 30 at 13:02
    
I think that Postgres can retrieve both MAX and MIN efficiently, no matter if the column has ASC or DESC in the index. –  ypercube Jan 30 at 13:35
show 7 more comments
  1. In general, for many DBMSes to solve the function MAX(x) using an index, a descending order index on x is needed: this index has the property that the MAX value of x is the first value in the index.

    PostgreSQL supports a "backwards index scan" which, as its name suggests, scans the index in reverse order, so that the MAX value is the first value it finds in an index in normal (ascending) order.

  2. Then to find the MAX(x) for a key value, eg select MAX(x) from mytable where id = 27 will require a composite index:

    • the first column needs to be id so that the data for the given id can be found quickly
    • the second column should be x descending so that the first value of the next level of the index is the MAX value of x (or in PostgreSQL, in "normal"/ascending order).
  3. To find multiple max values in one query, one needs to create multiple indexes:

    • compound indexes are useful when one is specifying more conditions in the where clause, eg col1 = 27, col2 = 'black', while
    • multiple indexes are useful when retrieving multiple columns or expressions for the same where clause. In this case you enable the DBMS to perform two index scans and combine the results.

Putting all the above together, you should define two indexes:

create index on agenzia.SystemDataByDocument(docId, creatingDate desc);

and

create index on agenzia.SystemDataByDocument(docId, lastChangeDate desc);

In PostgreSQL the second columns of these index could be stored in "normal"/ascending order.

share|improve this answer
add comment

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.