Re: [PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Joost Kraaijeveld Sent: 07 November 2005 04:26 To: Tom Lane Cc: Pgsql-Performance Subject: Re: [PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware Hi Tom, On Sun, 2005-11-06 at 15:26 -0500, Tom Lane wrote: I'm confused --- where's the 82sec figure coming from, exactly? From actually executing the query. From PgAdmin: -- Executing query: select objectid from prototype.orders Total query runtime: 78918 ms. Data retrieval runtime: 188822 ms. 1104379 rows retrieved. We've heard reports of performance issues in PgAdmin with large result sets ... if you do the same query in psql, what happens? [EMAIL PROTECTED]:~/postgresql$ time psql muntdev -c select objectid from prototype.orders output.txt real0m5.554s user0m1.121s sys 0m0.470s Now *I* am confused. What does PgAdmin do more than giving the query to the database? Nothing - it just uses libpq's pqexec function. The speed issue in pgAdmin is rendering the results in the grid which can be slow on some OS's due to inefficiencies in some grid controls with large data sets. That's why we give 2 times - the first is the query runtime on the server, the second is data retrieval and rendering (iirc, it's been a while). Regards, Dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware
Hi Dave, On Mon, 2005-11-07 at 08:51 +, Dave Page wrote: On Sun, 2005-11-06 at 15:26 -0500, Tom Lane wrote: I'm confused --- where's the 82sec figure coming from, exactly? From actually executing the query. From PgAdmin: -- Executing query: select objectid from prototype.orders Total query runtime: 78918 ms. Data retrieval runtime: 188822 ms. 1104379 rows retrieved. We've heard reports of performance issues in PgAdmin with large result sets ... if you do the same query in psql, what happens? [EMAIL PROTECTED]:~/postgresql$ time psql muntdev -c select objectid from prototype.orders output.txt real0m5.554s user0m1.121s sys 0m0.470s Now *I* am confused. What does PgAdmin do more than giving the query to the database? Nothing - it just uses libpq's pqexec function. The speed issue in pgAdmin is rendering the results in the grid which can be slow on some OS's due to inefficiencies in some grid controls with large data sets. That's why we give 2 times - the first is the query runtime on the server, the second is data retrieval and rendering (iirc, it's been a while). That is what I thought, but what could explain the difference in query runtime (78 seconds versus 5 seconds) ? -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(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] Performance PG 8.0 on dual opteron / 4GB / 3ware
-Original Message- From: Joost Kraaijeveld [mailto:[EMAIL PROTECTED] Sent: 07 November 2005 09:03 To: Dave Page Cc: Tom Lane; Pgsql-Performance Subject: RE: [PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware Nothing - it just uses libpq's pqexec function. The speed issue in pgAdmin is rendering the results in the grid which can be slow on some OS's due to inefficiencies in some grid controls with large data sets. That's why we give 2 times - the first is the query runtime on the server, the second is data retrieval and rendering (iirc, it's been a while). That is what I thought, but what could explain the difference in query runtime (78 seconds versus 5 seconds) ? Not in terms of our code - we obviously do a little more than just run the query, but I can't spot anything in there that should be non-constant time. Don't suppose it's anything as simple as you vacuuming in between is it? Regards, Dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Performance problem with pg8.0
Jeroen van Iddekinge wrote: Hello, I have some strange performance problems with quering a table.It has 5282864, rows and contains the following columns : id ,no,id_words,position,senpos and sentence all are integer non null. Index on : * no * no,id_words * id_words * senpos, sentence, no) * d=primary key select count(1) from words_in_text takes 9 seconds to compleet. Because it's reading through the whole table. See mailing list archives for discussion of why it doesn't just use an index. The query 'select * from words_in_text' takes a verry long time to return the first record (more that 2 minutes) why? A long time for the first row, hardly any time for the others. That's because it assembles all the rows and returns them at the same time. If you don't want all the rows at once use a cursor. Also the following query behaves strange. select * from words_in_text where no 100 order by no; explain shows that pg is using sequence scan. When i turn of sequence scan, index scan is used and is faster. I have a 'Explain verbose analyze' of this query is at the end of the mail. It's just the explain analyze that's needed - the verbose gives far more detail than you'll want at this stage. The number of estimated rows is wrong, so I did 'set statistics 1000' on column no. After this the estimated number of rows was ok, but pg still was using seq scan. I don't see the correct row estimate - it looks like it's getting it wrong again to me. Can anyone explain why pg is using sequence and not index scan? There's one of two reasons: 1. It thinks it's going to fetch more rows than it does. 2. It has the relative costs of a seq-scan vs index accesses wrong. Can you try an EXPLAIN ANALYZE of select * from words_in_text where no 100 AND no = 0 order by no; Substitute whatever lower bound is sensible for no. Let's see if that gives the system a clue. Then, we'll need to look at your other tuning settings. Have you made any changes to your postgresql.conf settings, in particular those mentioned here: http://www.powerpostgresql.com/PerfList -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Temporary Table
Christian Paul B. Cosinas wrote: Does Creating Temporary table in a function and NOT dropping them affects the performance of the database? The system will drop it automatically, so it shouldn't affect. What _could_ be affecting you if you execute that function a lot, is accumulated bloat in pg_class, pg_attribute, or other system catalogs. You may want to make sure these are vacuumed often. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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] Performance PG 8.0 on dual opteron / 4GB / 3ware Raid5 / Debian??
Where are the pg_xlog and data directories with respect to each other? From this IOStat it looks like they might be on the same partition, which is not ideal, and actualy surprising that throughput is this good. You need to seperate pg_xlog and data directories to get any kind of reasonable performance. Also don't use RAID 5 - RAID 5 bites, no really - it bites. Use multiple RAID 1s, or RAID 10s, you will get better performance. 50MB/70MB is about the same as you get from a single disk or a RAID 1. We use 2x9506S8MI controlers, and have maintained excellent performance with 2xRAID 10 and 2xRAID 1. Make sure you get the firmware update if you have these controllers though. Alex Turner NetEconomist On 11/6/05, Joost Kraaijeveld [EMAIL PROTECTED] wrote: Hi, I am experiencing very long update queries and I want to know if it reasonable to expect them to perform better. The query below is running for more than 1.5 hours (5500 seconds) now, while the rest of the system does nothing (I don't even type or move a mouse...). - Is that to be expected? - Is 180-200 tps with ~ 9000 KB (see output iostat below) not low, given the fact that fsync is off? (Note: with bonnie++ I get write performance 50 MB/sec and read performace 70 MB/sec with 2000 read/write ops /sec? - Does anyone else have any experience with the 3Ware RAID controller (which is my suspect)? - Any good idea how to determine the real botleneck if this is not the performance I can expect? My hard- and software: - PostgreSQL 8.0.3 - Debian 3.1 (Sarge) AMD64 - Dual Opteron - 4GB RAM - 3ware Raid5 with 5 disks Pieces of my postgresql.conf (All other is default): shared_buffers = 7500 work_mem = 260096 fsync=false effective_cache_size = 32768 The query with explain (amount and orderbedrag_valuta are float8, ordernummer and ordernumber int4): explain update prototype.orders set amount = odbc.orders.orderbedrag_valuta from odbc.orders where ordernumber = odbc.orders.ordernummer; QUERY PLAN - Hash Join (cost=50994.74..230038.17 rows=1104379 width=466) Hash Cond: (outer.ordernumber = inner.ordernummer) - Seq Scan on orders (cost=0.00..105360.68 rows=3991868 width=455) - Hash (cost=48233.79..48233.79 rows=1104379 width=15) - Seq Scan on orders (cost=0.00..48233.79 rows=1104379 width=15) Sample output from iostat during query (about avarage): Device:tpskB_read/skB_wrtn/skB_readkB_wrtn hdc 0.00 0.00 0.00 0 0 sda 0.00 0.00 0.00 0 0 sdb 187.1323.76 8764.36 24 8852 -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: [EMAIL PROTECTED] web: www.askesis.nl ---(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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] 8.1 iss
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). Queries that previously seemed to stall out are still a little slow but nothing like before. And I'm seeing a more normal balance of CPU and disk i/o while a query is running instead of the high-cpu-low-disk-read situation I was seeing before. Concurrency is way up. I tried a couple of interim sizes for work_mem and so far, the larger the better (the server has 16Gb). I'll test a little larger size this evening and see what it does. Yes, I've read the warning that this is per process. Kudos to you Greg, thanks Luke for your comment (though it seems to disagree with my experience). Also to Dennis, there were not drastic changes in the plan between 8.0 and 8.1, it was just the actual execution times. Martin PostgreSQL [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] SELECT v_barcode, count(v_barcode) FROM lead GROUP BY v_barcode HAVING count(*) 1; This is a pretty good example of the place where 8.1 seems to be quite broken. ... ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Performance PG 8.0 on dual opteron / 4GB / 3ware
Dave Page wrote: Now *I* am confused. What does PgAdmin do more than giving the query to the database? Nothing - it just uses libpq's pqexec function. The speed issue in pgAdmin is rendering the results in the grid which can be slow on some OS's due to inefficiencies in some grid controls with large data sets. That's why we give 2 times - the first is the query runtime on the server, the second is data retrieval and rendering (iirc, it's been a while). yrnc. Query runtime includes data transfer to the client, i.e. until libpq returns the set, second time is retrieving data from libpq and rendering. Regards, ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] Index + mismatching datatypes [WAS: index on custom function; explain]
* Yann Michel [EMAIL PROTECTED] wrote: 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 I've got a similar problem: I have to match different datatypes, ie. bigint vs. integer vs. oid. Of course I tried to use casted index (aka ON (foo::oid)), but it didn't work. What am I doing wrong ? cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgreSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Index + mismatching datatypes [WAS: index on custom
On Mon, 2005-07-11 at 19:07 +0100, Enrico Weigelt wrote: I've got a similar problem: I have to match different datatypes, ie. bigint vs. integer vs. oid. Of course I tried to use casted index (aka ON (foo::oid)), but it didn't work. Don't include the cast in the index definition, include it in the query itself: SELECT ... FROM foo WHERE int8col = 5::int8 for example. Alternatively, upgrade to 8.0 or better, which doesn't require this workaround. -Neil ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] Figuring out which command failed
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 ---(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] Temporary Table
Alvaro Herrera wrote: Christian Paul B. Cosinas wrote: Does Creating Temporary table in a function and NOT dropping them affects the performance of the database? The system will drop it automatically, so it shouldn't affect. What _could_ be affecting you if you execute that function a lot, is accumulated bloat in pg_class, pg_attribute, or other system catalogs. You may want to make sure these are vacuumed often. The answer in my experience is a very loud YES YES YES If you use lots of temporary tables you will grow and dirty your system catalogs, so you need to be vacuuming them regularly also (pg_call, pg_attribute) Otherwise your db will slow to a crawl after a while. Ralph ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Temporary Table
In what directory in my linux server will I find these 3 tables? -Original Message- From: Alvaro Nunes Melo [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 26, 2005 10:49 AM To: Christian Paul B. Cosinas Subject: Re: [PERFORM] Temporary Table Christian Paul B. Cosinas wrote: I am creating a temporary table in every function that I execute. Which I think is bout 100,000 temporary tables a day. I think that a lot. ;) What is the command for vacuuming these 3 tables? VACUUM FULL pg_class; VACUUM FULL pg_attribute; VACUUM FULL pg_depend; I'm using this ones. Before using them, take a look in the size that this tables are using in your HD, and compare to what you get after running this commands. I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Temporary Table
I try to run this command in my linux server. VACUUM FULL pg_class; VACUUM FULL pg_attribute; VACUUM FULL pg_depend; But it give me the following error: -bash: VACUUM: command not found I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Temporary Table
Christian Paul B. Cosinas wrote: I try to run this command in my linux server. VACUUM FULL pg_class; VACUUM FULL pg_attribute; VACUUM FULL pg_depend; But it give me the following error: -bash: VACUUM: command not found That needs to be run from psql ... I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Temporary Table
Ummm...they're SQL commands. Run them in PostgreSQL, not on the unix command line... Christian Paul B. Cosinas wrote: I try to run this command in my linux server. VACUUM FULL pg_class; VACUUM FULL pg_attribute; VACUUM FULL pg_depend; But it give me the following error: -bash: VACUUM: command not found I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Temporary Table
In what directory in my linux server will I find these 3 tables? Directory? They're tables in your database... ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Temporary Table
I see. But How Can I put this in the Cron of my Linux Server? I really don't have an idea :) What I want to do is to loop around all the databases in my server and execute the vacuum of these 3 tables in each tables. -Original Message- From: Joshua D. Drake [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 08, 2005 2:11 AM To: Christian Paul B. Cosinas Cc: 'Alvaro Nunes Melo'; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Temporary Table Christian Paul B. Cosinas wrote: I try to run this command in my linux server. VACUUM FULL pg_class; VACUUM FULL pg_attribute; VACUUM FULL pg_depend; But it give me the following error: -bash: VACUUM: command not found That needs to be run from psql ... I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Temporary Table
Or you could just run the 'vacuumdb' utility... Put something like this in cron: # Vacuum full local pgsql database 30 * * * * postgres vacuumdb -a -q -z You really should read the manual. Chris Christian Paul B. Cosinas wrote: I see. But How Can I put this in the Cron of my Linux Server? I really don't have an idea :) What I want to do is to loop around all the databases in my server and execute the vacuum of these 3 tables in each tables. -Original Message- From: Joshua D. Drake [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 08, 2005 2:11 AM To: Christian Paul B. Cosinas Cc: 'Alvaro Nunes Melo'; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Temporary Table Christian Paul B. Cosinas wrote: I try to run this command in my linux server. VACUUM FULL pg_class; VACUUM FULL pg_attribute; VACUUM FULL pg_depend; But it give me the following error: -bash: VACUUM: command not found That needs to be run from psql ... I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Temporary Table
You can use the vacuumdb external command. Here's an example: vacuumdb --full --analyze --table mytablename mydbname On Tue, 8 Nov 2005, Christian Paul B. Cosinas wrote: But How Can I put this in the Cron of my Linux Server? I really don't have an idea :) What I want to do is to loop around all the databases in my server and execute the vacuum of these 3 tables in each tables. -Original Message- From: Joshua D. Drake [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 08, 2005 2:11 AM To: Christian Paul B. Cosinas Cc: 'Alvaro Nunes Melo'; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Temporary Table Christian Paul B. Cosinas wrote: I try to run this command in my linux server. VACUUM FULL pg_class; VACUUM FULL pg_attribute; VACUUM FULL pg_depend; But it give me the following error: -bash: VACUUM: command not found That needs to be run from psql ... I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Jeff Frost, Owner [EMAIL PROTECTED] Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] Expensive function and the optimizer
I have a function, call it myfunc(), that is REALLY expensive computationally. Think of it like, If you call this function, it's going to telephone the Microsoft Help line and wait in their support queue to get the answer. Ok, it's not that bad, but it's so bad that the optimizer should ALWAYS consider it last, no matter what. (Realistically, the function takes 1-2 msec average, so applying it to 40K rows takes 40-80 seconds. It's a graph-theory algorithm, known to be NP-complete.) Is there some way to explain this cost to the optimizer in a permanent way, like when the function is installed? Here's what I get with one critical query (somewhat paraphrased for simplicity): explain analyze select A.ID from A join B ON (A.ID = B.ID) where A.row_num = 0 and A.row_num = 43477 and B.ID = 52 and myfunc(A.FOO, 'FooBar') order by row_num; QUERY PLAN -- Nested Loop (cost=0.00..72590.13 rows=122 width=8) - Index Scan using i_a_row_num on a (cost=0.00..10691.35 rows=1 width=8) Index Cond: ((row_num = 0) AND (row_num = 43477)) Filter: myfunc((foo)::text, 'FooBar'::text) - Index Scan using i_b_id on b (cost=0.00..5.05 rows=1 width=4) Index Cond: (outer.id = b.id) Filter: (id = 52) Total runtime: 62592.631 ms (8 rows) Notice the Filter: myfunc(...) that comes in the first loop. This means it's applying myfunc() to 43477 rows in this example. The second index scan would cut this number down from 43477 rows to about 20 rows, making the query time drop from 62 seconds down to a fraction of a second. Is there any way to give Postgres this information? The only way I've thought of is something like this: select X.id from (select A.id, A.foo, A.row_num from A join B ON (A.id = B.id) where A.row_num = 0 and A.row_num = 43477 and B.id = 52) as X where myfunc(X.foo, 'FooBar') order by X.row_num; I can do this, but it means carefully hand-crafting each query rather than writing a more natural query. Thanks, Craig ---(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] Temporary Table
On Tue, 2005-11-08 at 10:22 +, Christian Paul B. Cosinas wrote: I see. But How Can I put this in the Cron of my Linux Server? I really don't have an idea :) What I want to do is to loop around all the databases in my server and execute the vacuum of these 3 tables in each tables. I usually write a small shell script something like: == #!/bin/sh psql somedatabase EOQ VACUUM this; VACUUM that; DELETE FROM someotherplace WHERE delete_this_record; EOQ == and so forth... This makes the SQL quite nicely readable. Regards, Andrew McMillan. - Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/PHYS: Level 2, 150-154 Willis St DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267 You work very hard. Don't try to think as well. - signature.asc Description: This is a digitally signed message part
Re: [PERFORM] Expensive function and the optimizer
Craig A. James [EMAIL PROTECTED] writes: Is there some way to explain this cost to the optimizer in a permanent way, Nope, sorry. One thing you could do in the particular case at hand is to rejigger the WHERE clause involving the function so that it requires values from both tables and therefore can't be applied till after the join is made. (If nothing else, give the function an extra dummy argument that can be passed as a variable from the other table.) This is an ugly and non-general solution of course. The only way I've thought of is something like this: select X.id from (select A.id, A.foo, A.row_num from A join B ON (A.id = B.id) where A.row_num = 0 and A.row_num = 43477 and B.id = 52) as X where myfunc(X.foo, 'FooBar') order by X.row_num; As written, that won't work because the planner will happily flatten the query to the same thing you had before. You can put an OFFSET 0 into the sub-select to prevent that from happening, but realize that this creates a pretty impervious optimization fence ... the side-effects might be undesirable when you come to look at real queries instead of toy cases. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
