[PERFORM] Index Scan Costs versus Sort

This is related to my post the other day about sort performance. Part of my problem seems to be that postgresql is greatly overestimating the cost of index scans. As a result, it prefers query plans that involve seq scans and sorts versus query plans that use index scans. Here is an example

Re: [PERFORM] Index Scan Costs versus Sort

at the county level (but from a quick look at the data I don't see any, and the correlation factor indicates there isn't any if I am interpreting it correctly). Any other info that would be helpful to see? Charlie Tom Lane wrote: Charlie Savage [EMAIL PROTECTED] writes: 1. Postgresql estimates

Re: [PERFORM] Index Scan Costs versus Sort

Following up with some additional information. The machine has 1Gb physical RAM. When I run the query (with sort and seqscan enabled), top reports (numbers are fairly consistent): Mem: 1,032,972k total, 1,019,516k used, 13,412k free, 17,132k buffers Swap: 2,032,140k total, 17,592k used,

Re: [PERFORM] Sort performance on large tables

Hi Simon, Thanks for the response Simon. PostgreSQL can do HashAggregates as well as GroupAggregates, just like Oracle. HashAggs avoid the sort phase, so would improve performance considerably. The difference in performance you are getting is because of the different plan used. Did you

[PERFORM] Sort performance on large tables

Hi everyone, I have a question about the performance of sort. Setup: Dell Dimension 3000, Suse 10, 1GB ram, PostgreSQL 8.1 RC 1 with PostGIS, 1 built-in 80 GB IDE drive, 1 SATA Seagate 400GB drive. The IDE drive has the OS and the WAL files, the SATA drive the database. From hdparm the max

Re: [PERFORM] Sort performance on large tables

degraded back to the original values. Charlie Richard Huxton wrote: Charlie Savage wrote: Hi everyone, I have a question about the performance of sort. Note it takes over 10 times longer to do the sort than the full sequential scan. Should I expect results like this? I realize

Re: [PERFORM] Sort performance on large tables

Its an int4. Charlie Tom Lane wrote: Charlie Savage [EMAIL PROTECTED] writes: Thus the time decreased from 8486 seconds to 5279 seconds - which is a nice improvement. However, that still leaves postgresql about 9 times slower. BTW, what data type are you sorting, exactly? If it's

Re: [PERFORM] Sort performance on large tables

have an index on tlid. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Charlie Savage Sent: Tuesday, November 08, 2005 2:05 AM To: pgsql-performance@postgresql.org Subject: [PERFORM] Sort performance on large tables Hi everyone, I have a question