Re: Optimising a query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Paul Lambert wrote:
<snip>


This part of the query alone takes a significant part of the time:

SELECT DISTINCT ON (finbalance.dealer_id, finbalance.year_id, finbalance.subledger_id, finbalance.account_id)
			finbalance.year_id AS year,
			finbalance.dealer_id AS dealer_id,
			lpad(finbalance.subledger_id::text,4,'0') AS subledger,
			lpad(finbalance.account_id::text,4,'0') AS account
		FROM finbalance

Runs with a query plan of :

"Unique (cost=30197.98..32782.33 rows=20675 width=16) (actual time=5949.695..7197.475 rows=17227 loops=1)" " -> Sort (cost=30197.98..30714.85 rows=206748 width=16) (actual time=5949.691..7018.931 rows=206748 loops=1)"
"        Sort Key: dealer_id, year_id, subledger_id, account_id"
"        Sort Method:  external merge  Disk: 8880kB"
" -> Seq Scan on finbalance (cost=0.00..8409.70 rows=206748 width=16) (actual time=0.042..617.949 rows=206748 loops=1)"
"Total runtime: 7210.966 ms"


So basically selecting from the finbalance table (approx. 206,000 records) takes 10 seconds, even longer without the distinct clause in there - the distinct collapses the result-set down to around 17,000 rows.

Taking out the two lpad's in there knocks off about 1500ms, so I can come up with something else for them - but I'd like to get the query as a whole down to under a second.

dealer_id, year_id, subledger_id and account_id are all part of the primary key on the finbalance table, so I don't think I can index them down any further.

Are there any config settings that would make it faster...

I'm running on a Quad-core pentium Xeon 1.6GHZ server with 4GB RAM. I imagine shared_buffers (32MB) and work_mem (1MB) could be bumped up a good bit more with 4GB of available RAM?


--
Paul Lambert
Database Administrator
AutoLedgers - A Reynolds & Reynolds Company

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

               http://www.postgresql.org/about/donate

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux