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.

I have a query to a table in Postgres with an order based on a date field and a number field, this table has 1000000 records

The data types of the table are:

fcv_id = serial
fcv_fecha_comprobante = timestamp without time zone
fcv_numero_comprobante = varchar(60)

The query is:

SELECT fcv_id, fcv_fecha_comprobante FROM factura_venta
ORDER BY fcv_fecha_comprobante, fcv_numero_comprobante

This query takes about 5 seconds, but if I take out the "order by" the query takes only 0.499 seconds

The problem I have is that I need to run this query in the shortest time possible, so I search on google what can I do and create a composite index with the following query

CREATE INDEX factura_venta_orden ON factura_venta
USING btree (fcv_fecha_comprobante ASC NULLS LAST
           , fcv_numero_comprobante ASC NULLS LAST);
ALTER TABLE factura_venta CLUSTER ON factura_venta_orden;

But the query is taking the same time or even more.

I'm using Postgres 9.0.13, here is the EXPLAIN with 73436 rows

Sort  (cost=11714.03..11897.62 rows=73436 width=27) (actual time=1260.759..1579.853 rows=73436 loops=1)
  Sort Key: fcv_fecha_comprobante, fcv_numero_comprobante
  Sort Method:  external merge  Disk: 2928kB
  ->  Seq Scan on factura_venta  (cost=0.00..4018.36 rows=73436 width=27) (actual time=0.363..162.558 rows=73436 loops=1)
Total runtime: 1694.882 ms

Postgres is running on a Phenon II 1055T (3 cores) With 8 GB Ram and 500 GB disk.

How I can optimize this query?

share|improve this question
    
Which PostgreSQL version do you use? Also, please edit the result of the EXPLAIN ANALYZE into your post. –  dezso Aug 23 '13 at 18:34
    
i append the explain to the post, thx –  aegis Aug 23 '13 at 18:40
    
How is it that your query has no WHERE clause and there are still only about 70k rows instead of 1M? –  dezso Aug 23 '13 at 18:48
    
Cross-posted on SO. I suppose you close the one on SO. –  Erwin Brandstetter Aug 23 '13 at 19:50
add comment

2 Answers 2

up vote 2 down vote accepted

Answer transfered from the cross-posted question on SO.

As derived from your comment on the other post, you are loading all rows into a java module to conduct a search there. But searching is better done in the database itself - that's what a database is good at. Only return the rows you actually need.

If you really need all rows, there are many little things to make this faster. 1M rows will never be very fast, though.

Postgres 9.2

Upgrading to the current version of Postgres 9.2 would go a long way. The focus for this release was big data. Then you could also make the index covering by adding fcv_id to it:

CREATE INDEX factura_venta_orden
ON factura_venta (fcv_fecha_comprobante, fcv_numero_comprobante, fcv_id);

This way (provided, the table isn't updated too much), you could retrieve the result from the index directly without the need to sort.

Note how the additional column comes last, since it does not contribute to the sort order in the query. More on the order of columns in a multi-column index.

CLUSTER / pg_repack

I see you already found CLUSTER. You are aware that this is a one-time operation, that should help your cause, but needs to be re-run after enough updates?

There is also the community tool pg_repack as replacement for VACUUM FULL / CLUSTER.

work_mem

This line in your output from EXPLAIN ANALYZE:

Sort Method:  external merge  Disk: 2928kB

tells us, that sorting is not done in RAM, which is expensive. You could probably improve performance a lot by tuning the according setting for work_mem

work_mem (integer)

Specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files. ...

Setting this too high may have adverse effects. Read the manual carefully. Consider increasing the setting for only your session with the big query:

SET work_mem  = '50MB';
SELECT ...;

If you don't terminate the session afterwards (connection pooling), run:

RESET work_mem;

to reset it.

50 MB are an estimate based on your EXPLAIN ANALYZE output for 73k rows. Test with 1M rows to get the actual amount you need.

share|improve this answer
add comment

The basic problem of your query is that it has no WHERE clause. Usually, when PostgreSQL has to retrieve all (or at least a large proprtion of) the rows in a table, it will perform a sequential scan - simply because it is cheaper than first go to the index, locate all the rows and then get all of them from the table regardless.

If you happened to use version 9.2, there are chances that a covering index could help a bit (in the case it has substantially less columns then the table itself).

This index could look like

CREATE INDEX factura_venta_orden
ON factura_venta USING btree (fcv_fecha_comprobante, fcv_numero_comprobante, fcv_id);

But the biggest problem is that this still will be relatively slow as 1 million rows of this index eat up 100 MB. If it is not cached, it will have to be read from disk when accessed and - depending on your I/O subsystem - this can be slow.

The really slow part of your query is the sorting. The query plan show an external merge sort on disk. This could be easily pushed into the memory by raising work_mem over 3 MB (to be on the safe side, let it be 10 MB). I would expect this to shave off a second at least from the 1.5 you show above.

share|improve this answer
    
It is reassuring, that you came up with the same major points in parallel. :) –  Erwin Brandstetter Aug 23 '13 at 19:49
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.