[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 IO for the IDE drive is about 50Mb/s and the SATA drive is about 65Mb/s. Thus a very low-end machine - but it used just for development (i.e., it is not a production machine) and the only thing it does is run a PostgresSQL database. I have a staging table called completechain that holds US tiger data (i.e., streets and addresses for the US). The table is approximately 18GB. Its big because there is a lot of data, but also because the table is not normalized (it comes that way). I want to extract data out of the file, with the most important values being stored in a column called tlid. The tlid field is an integer, and the values are 98% unique. There is a second column called ogc_fid which is unique (it is a serial field). I need to extract out unique TLID's (doesn't matter which duplicate I get rid of). To do this I am running this query: SELECT tlid, min(ogc_fid) FROM completechain GROUP BY tlid; The results from explain analyze are: "GroupAggregate (cost=10400373.80..11361807.88 rows=48071704 width=8) (actual time=7311682.715..8315746.835 rows=47599910 loops=1)" " -> Sort (cost=10400373.80..10520553.06 rows=48071704 width=8) (actual time=7311682.682..7972304.777 rows=48199165 loops=1)" "Sort Key: tlid" "-> Seq Scan on completechain (cost=0.00..2228584.04 rows=48071704 width=8) (actual time=27.514..773245.046 rows=48199165 loops=1)" "Total runtime: 8486057.185 ms" Doing a similar query produces the same results: SELECT DISTINCT ON (tlid), tlid, ogc_fid FROM completechain; Note it takes over 10 times longer to do the sort than the full sequential scan. Should I expect results like this? I realize that the computer is quite low-end and is very IO bound for this query, but I'm still surprised that the sort operation takes so long. Out of curiosity, I setup an Oracle database on the same machine with the same data and ran the same query. Oracle was over an order of magnitude faster. Looking at its query plan, it avoided the sort by using "HASH GROUP BY." Does such a construct exist in PostgreSQL (I see only hash joins)? Also as an experiment I forced oracle to do a sort by running this query: SELECT tlid, min(ogc_fid) FROM completechain GROUP BY tlid ORDER BY tlid; Even with this, it was more than a magnitude faster than Postgresql. Which makes me think I have somehow misconfigured postgresql (see the relevant parts of postgresql.conf below). Any idea/help appreciated. Thanks, Charlie --- #--- # RESOURCE USAGE (except WAL) #--- shared_buffers = 4 # 4 buffers * 8192 bytes/buffer = 327,680,000 bytes #shared_buffers = 1000 # min 16 or max_connections*2, 8KB each temp_buffers = 5000 #temp_buffers = 1000# min 100, 8KB each #max_prepared_transactions = 5 # can be 0 or more # note: increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). work_mem = 16384# in Kb #work_mem = 1024# min 64, size in KB maintenance_work_mem = 262144# in kb #maintenance_work_mem = 16384 # min 1024, size in KB #max_stack_depth = 2048 # min 100, size in KB # - Free Space Map - max_fsm_pages = 6 #max_fsm_pages = 2 # min max_fsm_relations*16, 6 bytes each #max_fsm_relations = 1000 # min 100, ~70 bytes each # - Kernel Resource Usage - #max_files_per_process = 1000 # min 25 #preload_libraries = '' # - Cost-Based Vacuum Delay - #vacuum_cost_delay = 0 # 0-1000 milliseconds #vacuum_cost_page_hit = 1 # 0-1 credits #vacuum_cost_page_miss = 10 # 0-1 credits #vacuum_cost_page_dirty = 20# 0-1 credits #vacuum_cost_limit = 200# 0-1 credits # - Background writer - #bgwriter_delay = 200 # 10-1 milliseconds between rounds #bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned/round #bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round #bgwriter_all_percent = 0.333 # 0-100% of all buffers scanned/round #bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round #--- # WRITE AHEAD LOG #--
Re: [PERFORM] Sort performance on large tables
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 that the computer is quite low-end and is very IO bound for this query, but I'm still surprised that the sort operation takes so long. The sort will be spilling to disk, which will grind your I/O to a halt. work_mem = 16384# in Kb Try upping this. You should be able to issue "set work_mem = 10" before running your query IIRC. That should let PG do its sorting in larger chunks. Also, if your most common access pattern is ordered via tlid look into clustering the table on that. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] 8.1 iss
Am Montag, 7. November 2005 18:22 schrieb PostgreSQL: > My most humble apologies to the pg development team (pg_lets?). > > I took Greg Stark's advice and set: > > shared_buffers = 1 # was 5 > work_mem = 1048576# 1Gb - was 16384 > > Also, I noticed that the EXPLAIN ANALYZE consistently thought reads would > take longer than they actually did, so I decreased random_page_cost down to > 1 (the server has a SATA Raid at level 10). Don't do that, use 1.5 or 2, setting it to 1 will only work well if you have small databases fitting completly in memory. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Sort performance on large tables
Charlie, > Should I expect results like this? I realize that the > computer is quite low-end and is very IO bound for this > query, but I'm still surprised that the sort operation takes so long. It's the sort performance of Postgres that's your problem. > Out of curiosity, I setup an Oracle database on the same > machine with the same data and ran the same query. Oracle > was over an order of magnitude faster. Looking at its query > plan, it avoided the sort by using "HASH GROUP BY." Does > such a construct exist in PostgreSQL (I see only hash joins)? Yes, hashaggregate does a similar thing. You can force the planner to do it, don't remember off the top of my head but someone else on-list will. > Also as an experiment I forced oracle to do a sort by running > this query: > > SELECT tlid, min(ogc_fid) > FROM completechain > GROUP BY tlid > ORDER BY tlid; > > Even with this, it was more than a magnitude faster than Postgresql. > Which makes me think I have somehow misconfigured postgresql > (see the relevant parts of postgresql.conf below). Just as we find with a similar comparison (with a "popular commercial, proprietary database" :-) Though some might suggest you increase work_mem or other tuning suggestions to speed sorting, none work. In fact, we find that increasing work_mem actually slows sorting slightly. We are commissioning an improved sorting routine for bizgres (www.bizgres.org) which will be contributed to the postgres main, but won't come out at least until 8.2 comes out, possibly 12 mos. In the meantime, you will be able to use the new routine in the bizgres version of postgres, possibly in the next couple of months. Also - we (Greenplum) are about to announce the public beta of the bizgres MPP database, which will use all of your CPUs, and those of other nodes in a cluster, for sorting. We see a linear scaling of sort performance, so you could add CPUs and/or hosts and scale out of the problem. Cheers, - Luke ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Sort performance on large tables
I have run into this type of query problem as well. I solved it in my application by the following type of query. SELECT tlid FROM completechain AS o WHERE not exists ( SELECT 1 FROM completechain WHERE tlid=o.tlid and ogc_fid!=o.ogc_fid ); Assumes of course that you 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 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 IO for the IDE drive is about 50Mb/s and > the SATA drive is about 65Mb/s. Thus a very low-end machine > - but it used just for development (i.e., it is not a > production machine) and the only thing it does is run a > PostgresSQL database. > > I have a staging table called completechain that holds US > tiger data (i.e., streets and addresses for the US). The > table is approximately 18GB. Its big because there is a lot > of data, but also because the table is not normalized (it > comes that way). > > I want to extract data out of the file, with the most > important values being stored in a column called tlid. The > tlid field is an integer, and the values are 98% unique. > There is a second column called ogc_fid which is unique (it > is a serial field). I need to extract out unique TLID's > (doesn't matter which duplicate I get rid of). To do this I > am running this query: > > SELECT tlid, min(ogc_fid) > FROM completechain > GROUP BY tlid; > > The results from explain analyze are: > > "GroupAggregate (cost=10400373.80..11361807.88 rows=48071704 > width=8) (actual time=7311682.715..8315746.835 rows=47599910 loops=1)" > " -> Sort (cost=10400373.80..10520553.06 rows=48071704 > width=8) (actual time=7311682.682..7972304.777 rows=48199165 loops=1)" > "Sort Key: tlid" > "-> Seq Scan on completechain (cost=0.00..2228584.04 > rows=48071704 width=8) (actual time=27.514..773245.046 > rows=48199165 loops=1)" > "Total runtime: 8486057.185 ms" > > Doing a similar query produces the same results: > > SELECT DISTINCT ON (tlid), tlid, ogc_fid FROM completechain; > > Note it takes over 10 times longer to do the sort than the > full sequential scan. > > Should I expect results like this? I realize that the > computer is quite low-end and is very IO bound for this > query, but I'm still surprised that the sort operation takes so long. > > Out of curiosity, I setup an Oracle database on the same > machine with the same data and ran the same query. Oracle > was over an order of magnitude faster. Looking at its query > plan, it avoided the sort by using "HASH GROUP BY." Does > such a construct exist in PostgreSQL (I see only hash joins)? > > Also as an experiment I forced oracle to do a sort by running > this query: > > SELECT tlid, min(ogc_fid) > FROM completechain > GROUP BY tlid > ORDER BY tlid; > > Even with this, it was more than a magnitude faster than Postgresql. > Which makes me think I have somehow misconfigured postgresql > (see the relevant parts of postgresql.conf below). > > Any idea/help appreciated. > > Thanks, > > Charlie > > > --- > > #- > -- > # RESOURCE USAGE (except WAL) > #- > -- > > shared_buffers = 4 # 4 buffers * 8192 > bytes/buffer = 327,680,000 bytes > #shared_buffers = 1000# min 16 or > max_connections*2, 8KB each > > temp_buffers = 5000 > #temp_buffers = 1000 # min 100, 8KB each > #max_prepared_transactions = 5# can be 0 or more > # note: increasing max_prepared_transactions costs ~600 bytes > of shared memory # per transaction slot, plus lock space (see > max_locks_per_transaction). > > work_mem = 16384# in Kb > #work_mem = 1024 # min 64, size in KB > > maintenance_work_mem = 262144# in kb > #maintenance_work_mem = 16384 # min 1024, size in KB > #max_stack_depth = 2048 # min 100, size in KB > > # - Free Space Map - > > max_fsm_pages = 6 > #max_fsm_pages = 2# min > max_fsm_relations*16, 6 bytes each > > #max_fsm_relations = 1000 # min 100, ~70 bytes each > > # - Kernel Resource Usage - > > #max_files_per_process = 1000 # min 25 > #preload_libraries = '' > > # - Cost-Based Vacuum Delay - > > #vacuum_cost_delay = 0#
Re: [PERFORM] Sort performance on large tables
On Tue, 8 Nov 2005, Luke Lonergan wrote: > > SELECT tlid, min(ogc_fid) > > FROM completechain > > GROUP BY tlid > > ORDER BY tlid; > > > > Even with this, it was more than a magnitude faster than Postgresql. > > Which makes me think I have somehow misconfigured postgresql > > (see the relevant parts of postgresql.conf below). > > Just as we find with a similar comparison (with a "popular commercial, > proprietary database" :-) Though some might suggest you increase > work_mem or other tuning suggestions to speed sorting, none work. In > fact, we find that increasing work_mem actually slows sorting slightly. I wish you'd qualify your statements, because I can demonstrably show that I can make sorts go faster on my machine at least by increasing work_mem under some conditions. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Sort performance on large tables
Title: Re: [PERFORM] Sort performance on large tables Stephan, On 11/8/05 9:38 AM, "Stephan Szabo" <[EMAIL PROTECTED]> wrote: > > Just as we find with a similar comparison (with a "popular commercial, > proprietary database" :-) Though some might suggest you increase > work_mem or other tuning suggestions to speed sorting, none work. In > fact, we find that increasing work_mem actually slows sorting slightly. I wish you'd qualify your statements, because I can demonstrably show that I can make sorts go faster on my machine at least by increasing work_mem under some conditions. Cool – can you provide your test case please? I’ll ask our folks to do the same, but as I recall we did some pretty thorough testing and found that it doesn’t help. Moreover, the conclusion was that the current algorithm isn’t designed to use memory effectively. Recognize also that we’re looking for a factor of 10 or more improvement here – this is not a small increase that’s needed. - Luke
Re: [PERFORM] Sort performance on large tables
On Tue, 8 Nov 2005, Luke Lonergan wrote: > Stephan, > > On 11/8/05 9:38 AM, "Stephan Szabo" <[EMAIL PROTECTED]> wrote: > > >> > > >> > Just as we find with a similar comparison (with a "popular commercial, > >> > proprietary database" :-) Though some might suggest you increase > >> > work_mem or other tuning suggestions to speed sorting, none work. In > >> > fact, we find that increasing work_mem actually slows sorting slightly. > > > > I wish you'd qualify your statements, because I can demonstrably show that > > I can make sorts go faster on my machine at least by increasing work_mem > > under some conditions. > > > Cool ? can you provide your test case please? I probably should have added the wink smiley to make it obvious I was talking about the simplest case, things that don't fit in work_mem at the current level but for which it's easy to raise work_mem to cover. It's not a big a gain as one might hope, but it does certainly drop again. > Recognize also that we?re looking for a factor of 10 or more improvement > here ? this is not a small increase that?s needed. I agree that we definately need help on that regard. I do see the effect where raising work_mem lowers the performance up until that point. I just think that it requires more care in the discussion than disregarding the suggestions entirely especially since people are going to see this in the archives. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Sort performance on large tables
Thanks everyone for the feedback. I tried increasing work_mem: set work_mem to 30; select tlid, min(ogc_fid) from completechain group by tld; The results are: "GroupAggregate (cost=9041602.80..10003036.88 rows=48071704 width=8) (actual time=4371749.523..5106162.256 rows=47599910 loops=1)" " -> Sort (cost=9041602.80..9161782.06 rows=48071704 width=8) (actual time=4371690.894..4758660.433 rows=48199165 loops=1)" "Sort Key: tlid" "-> Seq Scan on completechain (cost=0.00..2228584.04 rows=48071704 width=8) (actual time=49.518..805234.970 rows=48199165 loops=1)" "Total runtime: 5279988.127 ms" Thus the time decreased from 8486 seconds to 5279 seconds - which is a nice improvement. However, that still leaves postgresql about 9 times slower. I tried increasing work_mem up to 50, but at that point the machine started using its swap partition and performance 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 that the computer is >> quite low-end and is very IO bound for this query, but I'm still >> surprised that the sort operation takes so long. > > The sort will be spilling to disk, which will grind your I/O to a halt. > >> work_mem = 16384# in Kb > > Try upping this. You should be able to issue "set work_mem = 10" > before running your query IIRC. That should let PG do its sorting in > larger chunks. > > Also, if your most common access pattern is ordered via tlid look into > clustering the table on that. 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 that the computer is quite low-end and is very IO bound for this query, but I'm still surprised that the sort operation takes so long. The sort will be spilling to disk, which will grind your I/O to a halt. work_mem = 16384# in Kb Try upping this. You should be able to issue "set work_mem = 10" before running your query IIRC. That should let PG do its sorting in larger chunks. Also, if your most common access pattern is ordered via tlid look into clustering the table on that. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Sort performance on large tables
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 a string type, what is your LC_COLLATE setting? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
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 a string type, what is your LC_COLLATE setting? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Sort performance on large tables
I'd set up a trigger to maintain summary tables perhaps... Chris Charlie Savage wrote: Thanks everyone for the feedback. I tried increasing work_mem: set work_mem to 30; select tlid, min(ogc_fid) from completechain group by tld; The results are: "GroupAggregate (cost=9041602.80..10003036.88 rows=48071704 width=8) (actual time=4371749.523..5106162.256 rows=47599910 loops=1)" " -> Sort (cost=9041602.80..9161782.06 rows=48071704 width=8) (actual time=4371690.894..4758660.433 rows=48199165 loops=1)" "Sort Key: tlid" "-> Seq Scan on completechain (cost=0.00..2228584.04 rows=48071704 width=8) (actual time=49.518..805234.970 rows=48199165 loops=1)" "Total runtime: 5279988.127 ms" Thus the time decreased from 8486 seconds to 5279 seconds - which is a nice improvement. However, that still leaves postgresql about 9 times slower. I tried increasing work_mem up to 50, but at that point the machine started using its swap partition and performance 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 that the computer is >> quite low-end and is very IO bound for this query, but I'm still >> surprised that the sort operation takes so long. > > The sort will be spilling to disk, which will grind your I/O to a halt. > >> work_mem = 16384# in Kb > > Try upping this. You should be able to issue "set work_mem = 10" > before running your query IIRC. That should let PG do its sorting in > larger chunks. > > Also, if your most common access pattern is ordered via tlid look into > clustering the table on that. 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 that the computer is quite low-end and is very IO bound for this query, but I'm still surprised that the sort operation takes so long. The sort will be spilling to disk, which will grind your I/O to a halt. work_mem = 16384# in Kb Try upping this. You should be able to issue "set work_mem = 10" before running your query IIRC. That should let PG do its sorting in larger chunks. Also, if your most common access pattern is ordered via tlid look into clustering the table on that. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM]
unsubscribe
Re: [PERFORM] Figuring out which command failed
On Nov 7, 2005, at 3:30 PM, Ralph Mason wrote: Hi, I have a transaction that has multiple separate command in it (nothing unusual there). However sometimes one of the sql statements will fail and so the whole transaction fails. In some cases I could fix the failing statement if only I knew which one it was. Can anyone think of any way to get which statement actually failed from the error message? If the error message gave me the line of the failure it would be excellent, but it doesn't. Perhaps it would be easy for me to patch my version of Postgres to do that? I realize I could do this with 2 phase commit, but that isn't ready yet! Any thoughts or ideas are much appreciated Thanks Ralph 2PC might not've been ready yesterday, but it's ready today! http://www.postgresql.org/docs/whatsnew -- Thomas F. O'Connell Database Architecture and Programming Co-Founder Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Sort performance on large tables
Very interesting technique. It doesn't actually do quite what I want since it returns all rows that do not have duplicates and not a complete list of unique tlid values. But I could massage it to do what I want. Anyway, the timing: "Seq Scan on completechain t1 (cost=0.00..218139733.60 rows=24099582 width=4) (actual time=25.890..3404650.452 rows=47000655 loops=1)" " Filter: (NOT (subplan))" " SubPlan" "-> Index Scan using idx_completechain_tlid on completechain t2 (cost=0.00..4.48 rows=1 width=0) (actual time=0.059..0.059 rows=0 loops=48199165)" " Index Cond: ($0 = tlid)" " Filter: ($1 <> ogc_fid)" "Total runtime: 3551423.162 ms" Marc Morin wrote: So a 60% reduction in time. Thanks again for the tip. Charlie I have run into this type of query problem as well. I solved it in my application by the following type of query. Assumes of course that you 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 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 IO for the IDE drive is about 50Mb/s and the SATA drive is about 65Mb/s. Thus a very low-end machine - but it used just for development (i.e., it is not a production machine) and the only thing it does is run a PostgresSQL database. I have a staging table called completechain that holds US tiger data (i.e., streets and addresses for the US). The table is approximately 18GB. Its big because there is a lot of data, but also because the table is not normalized (it comes that way). I want to extract data out of the file, with the most important values being stored in a column called tlid. The tlid field is an integer, and the values are 98% unique. There is a second column called ogc_fid which is unique (it is a serial field). I need to extract out unique TLID's (doesn't matter which duplicate I get rid of). To do this I am running this query: SELECT tlid, min(ogc_fid) FROM completechain GROUP BY tlid; The results from explain analyze are: "GroupAggregate (cost=10400373.80..11361807.88 rows=48071704 width=8) (actual time=7311682.715..8315746.835 rows=47599910 loops=1)" " -> Sort (cost=10400373.80..10520553.06 rows=48071704 width=8) (actual time=7311682.682..7972304.777 rows=48199165 loops=1)" "Sort Key: tlid" "-> Seq Scan on completechain (cost=0.00..2228584.04 rows=48071704 width=8) (actual time=27.514..773245.046 rows=48199165 loops=1)" "Total runtime: 8486057.185 ms" Doing a similar query produces the same results: SELECT DISTINCT ON (tlid), tlid, ogc_fid FROM completechain; Note it takes over 10 times longer to do the sort than the full sequential scan. Should I expect results like this? I realize that the computer is quite low-end and is very IO bound for this query, but I'm still surprised that the sort operation takes so long. Out of curiosity, I setup an Oracle database on the same machine with the same data and ran the same query. Oracle was over an order of magnitude faster. Looking at its query plan, it avoided the sort by using "HASH GROUP BY." Does such a construct exist in PostgreSQL (I see only hash joins)? Also as an experiment I forced oracle to do a sort by running this query: SELECT tlid, min(ogc_fid) FROM completechain GROUP BY tlid ORDER BY tlid; Even with this, it was more than a magnitude faster than Postgresql. Which makes me think I have somehow misconfigured postgresql (see the relevant parts of postgresql.conf below). Any idea/help appreciated. Thanks, Charlie --- #- -- # RESOURCE USAGE (except WAL) #- -- shared_buffers = 4 # 4 buffers * 8192 bytes/buffer = 327,680,000 bytes #shared_buffers = 1000 # min 16 or max_connections*2, 8KB each temp_buffers = 5000 #temp_buffers = 1000# min 100, 8KB each #max_prepared_transactions = 5 # can be 0 or more # note: increasing max_prepared_transactions costs ~600 bytes of shared memory # per transaction slot, plus lock space (see max_locks_per_transaction). work_mem = 16384# in Kb #work_mem = 1024# min 64, size in KB maintenance_work_mem = 262144# in kb #maintenance_work_mem = 16384 # min 1024, size in KB #max_stack_depth = 2048 # min 100, size in KB # - Free Space Map - max_fsm_pag
