Re: [HACKERS] Moved simple_prompt()/sprompt.c
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Bruce Momjian writes: >> If a file is needed by three non-backend directories, /port seems to be >> the proper place for it. > src/port is intended for replacement implementations of standard library > functions. I concur, src/port is *not* the right place. src/port is for stuff that is platform-dependent. regards, tom lane ---(end of broadcast)--- TIP 3: 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: [HACKERS] Release changes
I'm not sure this is a fair assessment of statement level triggers. Yes, in MSSQL you can access the rows involved in the statement, but in Oracle you cannot (emphasis added): "Accessing Column Values in Row Triggers Within a trigger body of a *row trigger*, the PL/SQL code and SQL statements have access to the old and new column values of the current row affected by the triggering statement..." See: http://www.engin.umich.edu/caen/wls/software/oracle/appdev.901/a88876/adg13trg.htm#526 But PostgreSQL may be better than Oracle, don't you think? In the named document, - users are warned not to use recursiveness - INSTEAD OF triggers can only be row-level, and are quite restricted. PostgreSQLs rules are much more flexible. MSSQL does not have row level triggers at all (I know they didn't in 6.5, and looking at the help file it doesn't appear that they were added even in MSSQL 2000), so access at the statement level is needed to do anything useful at all. MSSQL2000 still doesn't have row level triggers, and I doubt that 2003 has. Just to make a clarification: I don't expect that something like this works: UPDATE NEW set foo = bar i.e. using the OLD and NEW rowsets as updateable rowsets, in the same way as row-level triggers allow simple NEW.foo := NEW.bar UPDATE footable set foo=bar FROM footable JOIN NEW USING (thekeycol) would be the style that's desirable, and this is the (only) way that MSSQL supports this. For this simple example, a row-level trigger certainly will be the cheapest way (both in respect to implementation and execution time), but if additional tables are needed query count will be multiplied by the number of rows affected. This effectively reduces mass execution to line-by-line execution. Regards, Andreas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] getting confused parsing ACLITEMS...
> Seriously, I think there's a good case for banning a few characters in > at least some names - like []<>'"~#*|\ , say Why? They're allowed in all other identifiers. And what if someone already has a database full of usernames with those chars? They wouldn't be able to load their dump properly... Chris > andrew > > Tom Lane wrote: > > >Andreas Pflug <[EMAIL PROTECTED]> writes: > > > > > >>Is it useful to allow these special chars at all? Seems this creates a > >>lot of work, and most admins will probably stick to "normal" user names > >>anyway. > >> > >> > > > >Well, the reason it's been left unfixed for so long is exactly that it > >didn't seem pressing. But if Chris wants to do the work, I won't stand > >in his way ... > > > > > > > > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] build on unixware 713
forwarded to the compiler guys at SCO. LER --On Thursday, August 07, 2003 19:10:04 +0200 [EMAIL PROTECTED] wrote: Hi Tom, Hi Larry After updating from cvs I'm going a little further. But still have problems: UX:acomp: ERROR: "fe-protocol3.c", line 1402: internal compiler error: can't deal with op BMOVE gmake[3]: *** [fe-protocol3.o] Error 1 gmake[2]: *** [all] Error 2 gmake[1]: *** [all] Error 2 gmake: *** [all] Error 2 UX:make: ERROR: fatal error. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] logging stuff
"Tom Lane" <[EMAIL PROTECTED]> writes: > Josh Berkus <[EMAIL PROTECTED]> writes: > > From my perspective, we could really use a "delimiter" between the > > "fields" of log output which is unlikely to appear within those fields > > instead of parsing by character count, rather than making dbname a > > special case. > > Well, Andrew was arguing that space is a likely character in dbnames, > but then again it's possible to put "]" into dbnames. I think the only > way to make this completely unambiguous would be to introduce a quoting > convention for dbnames (and usernames when those get added). But if > the goal is to allow simple parsing of log entries, that won't improve > matters at all. > > My feeling about it is that DBAs who want to automatically parse log > entries can just forbid spaces in the dbnames and usernames they allow. > The KISS principle applies here. > > A variant (which'd be okay with me) is to separate these fields with > tabs instead of spaces; then the rule for DBAs would be "don't allow > tabs in db/user names". The tabs are not properly treated by syslogd, look this piece of log: Aug 6 01:02:16 dell-01 postgres[19490]: [157296] LOG: connection received: host=[local] Aug 6 01:02:16 dell-01 postgres[19490]: [157297] LOG: connection authorized: user=kalman database=kalman Aug 6 01:02:16 dell-01 postgres[19490]: [157298] DEBUG: /usr/bin/postmaster child[19490]: starting with ( Aug 6 01:02:16 dell-01 postgres[19490]: [157299] DEBUG: ^Ipostgres Aug 6 01:02:16 dell-01 postgres[19490]: [157300] DEBUG: ^I-v131072 Aug 6 01:02:16 dell-01 postgres[19490]: [157301] DEBUG: ^I-p Aug 6 01:02:16 dell-01 postgres[19490]: [157302] DEBUG: ^Ikalman Aug 6 01:02:16 dell-01 postgres[19490]: [157303] DEBUG: ) do you see: ^I that are "chars" that syslogd hate. Regards Gaeatano Mendola ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] build on unixware 713
To fix it, remove -K inline from src/port/unixware's CFLAGS. Dave Prosser of SCO has a fix, but this is the most expedient fix for now. --On Thursday, August 07, 2003 18:58:16 -0500 Larry Rosenman <[EMAIL PROTECTED]> wrote: I've filed a bug with the folks I know that work on the compilers. workaround for now is to --enable-debug. LER --On Thursday, August 07, 2003 19:50:21 -0400 Bruce Momjian <[EMAIL PROTECTED]> wrote: The SCO compilers are notoriously buggy for the past 10-15 years. - -- Tom Lane wrote: [EMAIL PROTECTED] writes: > Larry just given me his own compiler and I still have the errors... [experiments a little...] Hmm. It works okay with -g, but fails with -O. I suggest filing a bug report. I'm not planning to spend any time looking for workarounds for SCO's compiler bugs. regards, tom lane ---(end of broadcast)--- TIP 3: 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 -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.4Beta1 hang?
Robert Creager <[EMAIL PROTECTED]> writes: > Anything to look at before I kick it? pg_locks and pg_stat_activity, if you can select from them in a non-stuck backend. > It's not built with debug, but I > can still get a backtrace. Might be useful. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] getting confused parsing ACLITEMS...
Tom Lane wrote: Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: So if you agree that there is a quoting problem,and you don't mind breaking backwards compatibility for it, I'll do a complete patch... I don't see any backwards-compatibility issue, because usernames containing double quotes just plain don't work in past releases; we've never before bothered to have a complete quoting solution in ACLs. Is it useful to allow these special chars at all? Seems this creates a lot of work, and most admins will probably stick to "normal" user names anyway. Regards, Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] build on unixware 713
I'm curious to know what that will do to performance. andrew Larry Rosenman wrote: To fix it, remove -K inline from src/port/unixware's CFLAGS. Dave Prosser of SCO has a fix, but this is the most expedient fix for now. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] consistency check on SPI tuple count failed
On Fri, 8 Aug 2003, Tom Lane wrote: > "Mendola Gaetano" <[EMAIL PROTECTED]> writes: > > Again the error: > > > kalman=# select bar(); > > ERROR: consistency check on SPI tuple count failed > > CONTEXT: PL/pgSQL function "bar" line 5 at for over select rows > > kalman=# select bar(); > > ERROR: consistency check on SPI tuple count failed > > CONTEXT: PL/pgSQL function "bar" line 5 at for over select rows > > server closed the connection unexpectedly > > This probably means the server terminated abnormally > > before or while processing the request. > > The connection to the server was lost. Attempting reset: Failed. > > After adding a second row to the test table, I am able to reproduce > the above (including the core dump after second try) on an intel/linux > box, but *not* on HPUX. > > I now suspect a memory-stomp kind of problem, like someone writing one > too many bytes in a struct. HPUX tends to mask these in situations > where intel will not, because it uses MAXALIGN 8 rather than 4. > > I have also just traced through _SPI_cursor_operation() in spi.c, > watched PortalRunFetch return 2, and then watched _SPI_checktuples read > zero from _SPI_current->processed. How the heck could that happen? > Compiler bug, or am I just crazy? Not sure, but I got the same thing. When I changed it to put the result in a temporary int variable and then put it in it started working for me (returning 0), reverting to the original made it fail again. I'm going to try -O0 and see what happens there. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.4beta1 build problem on unixware
On Sat, 9 Aug 2003, Kurt Roeckx wrote: > Date: Sat, 9 Aug 2003 17:45:55 +0200 > From: Kurt Roeckx <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Cc: Tom Lane <[EMAIL PROTECTED]>, > pgsql-hackers list <[EMAIL PROTECTED]>, > Larry Rosenman <[EMAIL PROTECTED]> > Subject: Re: [HACKERS] 7.4beta1 build problem on unixware > > On Thu, Aug 07, 2003 at 05:20:58PM +0200, [EMAIL PROTECTED] wrote: > > Hi Tom, > > > > I have NI_NUMERICHOST defined in netdb.h > > That's for getnameinfo(). getnameinfo() is older than > getaddrinfo() ... > > If you have that, I assume you have AI_NUMERICHOST in the same > file too ... ? > No > > Kurt > > -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 3: 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: [HACKERS] Adjustment of spinlock sleep delays
From: "Tom Lane" <[EMAIL PROTECTED]> > To forestall this scenario, I'm thinking of introducing backoff into the > sleep intervals --- that is, after first failure to get the spinlock, > sleep 10 msec; after the second, sleep 20 msec, then 40, etc, with a > maximum sleep time of maybe a second. The number of iterations would be > reduced so that we still time out after a minute's total delay. What about use the same algorithm used in ethernet when a collision is detected? When a collision occurs: 1) Stop sending. 2) Wait for some random amount of time between 0-T seconds 3) Try again. 4) If collision occurs again, increase T using an algorithm, go to Step 2. algorithm: T = rand ( 2n -1 ) * 51.2 ms where n- number of collisions Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Release changes
Thanks, Bruce On Tue, 5 Aug 2003, Bruce Momjian wrote: > > Done. > > --- > > Oleg Bartunov wrote: > > On Tue, 5 Aug 2003, Bruce Momjian wrote: > > > > > Oleg Bartunov wrote: > > > > Bruce, you forgot new contrib/tsearch2 module - full text extension > > > > (Oleg,Teodor) > > > > > > Sorry, added: > > > > > > New tsearch2 full-text search module (Oleg) > > > > Bruce, I wrote (Oleg,Teodor) > > > > > > > > > > > > Regards, > > Oleg > > _ > > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > > Sternberg Astronomical Institute, Moscow University (Russia) > > Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ > > phone: +007(095)939-16-83, +007(095)939-23-83 > > > > ---(end of broadcast)--- > > TIP 5: Have you checked our extensive FAQ? > > > >http://www.postgresql.org/docs/faqs/FAQ.html > > > > Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] getting confused parsing ACLITEMS...
> More to the point, this is highly incomplete... you did not teach the > adjacent getid routine about this, and there is code in (at least) > pg_dump.c that knows the quoting conventions used here. Hang on - those routines can parse the acls just fine? How? How do they handle usernames with equals signs in them (my major prob). How can it work at all? Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] src/interfaces/cli
Does anyone mind if I remove src/interfaces/cli? It's clearly outdated and useless. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Can't load a 7.3.4 dump into 7.4CVS
--On Saturday, August 09, 2003 11:47:43 -0500 Larry Rosenman <[EMAIL PROTECTED]> wrote: I tried(!) to load my 7.3.4 data into 7.4CVS. the Bricolage folks have managed to make a circular definition (at least not loadable). why does each setval() call invoke the pager? the dump I used is at: http://www.lerctr.org/~ler/pg.dump.gz $ ls -l pg.dump* -rw-r--r--1 ler isis 10989689 Aug 9 11:43 pg.dump.gz $ you need to have contrib/dbsize, contrib/pgstattuple, contrib/tsearch, contrib/dblink installed. Any ideas, folks? The load output is at: http://www.lerctr.org/~ler/pg734.load.gz $ ls -l pg734.load.gz -rw-r--r--1 ler isis 216248 Aug 9 12:25 pg734.load.gz $ -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] build on unixware 713
Ok, you're right it works with -g until... UX:cc: WARNING: debugging and optimization mutually exclusive; -O disabled Undefined first referenced symbol in file PQparameterStatus pg_backup_db.o UX:ld: ERROR: Symbol referencing errors. No output written to pg_dump gmake[3]: *** [pg_dump] Error 1 gmake[2]: *** [all] Error 2 gmake[1]: *** [all] Error 2 gmake: *** [all] Error 2 UX:make: ERROR: fatal error. Enough for tonight Good luck On Thu, 7 Aug 2003, Larry Rosenman wrote: > Date: Thu, 07 Aug 2003 16:10:13 -0500 > From: Larry Rosenman <[EMAIL PROTECTED]> > To: Tom Lane <[EMAIL PROTECTED]>, [EMAIL PROTECTED] > Cc: pgsql-hackers list <[EMAIL PROTECTED]> > Subject: Re: [HACKERS] build on unixware 713 > > Already done, but knowing that it's the same on the latest released > compiler is interesting. > > I'll pass it on. > > LER > > > --On Thursday, August 07, 2003 16:59:50 -0400 Tom Lane <[EMAIL PROTECTED]> > wrote: > > > [EMAIL PROTECTED] writes: > >> Larry just given me his own compiler and I still have the errors... > > > > [experiments a little...] Hmm. It works okay with -g, but fails with > > -O. > > > > I suggest filing a bug report. I'm not planning to spend any time > > looking for workarounds for SCO's compiler bugs. > > > > regards, tom lane > > > > ---(end of broadcast)--- > > TIP 3: 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 > > > > > > -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] new psql \d command
Bruce Momjian wrote: It might be a bit risky getting pg_dump to use it though? I don't think we every want pg_dump to use it --- better accurate than pretty in there. Agreed. There seems to be some tough assumptions that have to be made in that function that are better used for visual-only cases. Still, if there's something not precise, it should be cleared. Which tough assumptions are made that seem doubtful to you? Regards, Andreas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Make check problem.
When running make check, I get: The program /usr/local/pgsql/bin/postgres needed by initdb does not belong to PostgreSQL version 7.4beta1, or there may be a configuration problem. What's the point of running a regression tests if it's going to use the old binary? Kurt ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Make check problem.
On Sat, Aug 09, 2003 at 12:24:36PM -0400, Tom Lane wrote: > Kurt Roeckx <[EMAIL PROTECTED]> writes: > > What's the point of running a regression tests if it's going to > > use the old binary? > > It doesn't, ordinarily. Feel free to trace through the scripts and > find out what went wrong. I'm not sure what fixed it, but it seems a combination of make clean and running configure did it. Kurt ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] TODO: trigger features
Andreas Pflug <[EMAIL PROTECTED]> writes: > - Implement a way to enable triggers to check which columns are affected > by the triggering statement. This can already be done by comparing old and new values, no? I don't have a lot of sympathy for the idea that checking what the original UPDATE touched is a good shortcut. Rules or previous triggers might have changed additional columns. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] parallel regression test failure
On Fri, Jul 25, 2003 at 05:47:50PM -0400, Bruce Momjian wrote: > I am seeing the following parallel regression test failures. Any idea > on the cause? I think I saw about the same thing once, but I run the test again and it didn't show up anymore at all. I'm not sure what it exactly was, but it looked a bit simular to yours. Kurt ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] WITH HOLD and pooled connections
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Seems we have a problem with pooled connections and WITH HOLD cursors. > > We have code to reset transaction state and variables via RESET ALL, but > > how do we remove WITH HOLD cursors when we pass a connection to a new > > client? > > Prepared statements would be just as much of a problem. I think the > correct answer is simply "don't use those features in a pooled > environment". Yuck. I can't think of any other option. The pooled connections are all the same user, so there isn't any permission issue here. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] build on unixware 713
Hi Tom, Hi Larry After updating from cvs I'm going a little further. But still have problems: UX:acomp: ERROR: "fe-protocol3.c", line 1402: internal compiler error: can't deal with op BMOVE gmake[3]: *** [fe-protocol3.o] Error 1 gmake[2]: *** [all] Error 2 gmake[1]: *** [all] Error 2 gmake: *** [all] Error 2 UX:make: ERROR: fatal error. -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 6, Chemin d'Harraud Turrou +33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] WITH HOLD and pooled connections
Bruce Momjian <[EMAIL PROTECTED]> writes: > What would be interesting is a CREATE OR REPLACE functionality for > prepared cursors, where you could ask for it to be prepared, but if it > already existed, it would do nothing, or something like that. I don't think you could call that CREATE OR REPLACE, because (if it's supposed to be efficient) it *wouldn't* replace. In any case I think apps would prefer to avoid the overhead of even issuing such a command. If you approach it that way, you're adding some number of additional SQL commands to each pooled transaction, which seems to defeat the purpose of pre-preparing commands. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Correlation in cost_index()
> > Hrm, after an hour of searching and reading, I think one of the > > better papers on the subject can be found here: > > http://www.cs.ust.hk/faculty/dimitris/PAPERS/TKDE-NNmodels.pdf > > Interesting paper, but I don't see the connection to index order > correlation? Nothing that I found was nearly that specific, as close as I could find was the paper above on calculating the cost of fetching data from a disk, which I thought was the bigger problem at hand, but I digress... In one paper about large dimension index searches, they did suggest that cost was cumulative for the number of disk reads or nodes in the tree that weren't held in cache, which was the biggest hint that I had found on this specific topic. With that as a guiding light (or something faintly resembling it), it'd seem as though an avg depth of nodes in index * tuples_fetched * (random_io_cost * indexCorrelation) would be closer than where we are now... but now also think I/we're barking up the right tree with this thread. It's very possible that cost_index() is wrong, but it seems as though after some testing as if PostgreSQL _overly_ _favors_ the use of indexes: # SET enable_seqscan = true; SET enable_indexscan = true; SET SET # EXPLAIN ANALYZE SELECT * FROM report_user_cat_count AS rucc WHERE utc_date > '2002-10-01'::TIMESTAMP WITH TIME ZONE; INFO: cost_seqscan: run_cost: 21472.687500 startup_cost: 0.00 INFO: cost_index: run_cost: 21154.308116 startup_cost: 0.00 indexCorrelation: 0.999729 QUERY PLAN --- Index Scan using report_user_cat_count_utc_date_id_idx on report_user_cat_count rucc (cost=0.00..21154.31 rows=705954 width=64) (actual time=91.36..6625.79 rows=704840 loops=1) Index Cond: (utc_date > '2002-10-01 00:00:00-07'::timestamp with time zone) Total runtime: 11292.68 msec (3 rows) # SET enable_seqscan = true; SET enable_indexscan = false; SET SET # EXPLAIN ANALYZE SELECT * FROM report_user_cat_count AS rucc WHERE utc_date > '2002-10-01'::TIMESTAMP WITH TIME ZONE; INFO: cost_seqscan: run_cost: 21472.687500 startup_cost: 0.00 INFO: cost_index: run_cost: 21154.308116 startup_cost: 1.00 indexCorrelation: 0.999729 QUERY PLAN --- Seq Scan on report_user_cat_count rucc (cost=0.00..21472.69 rows=705954 width=64) (actual time=1091.45..7441.19 rows=704840 loops=1) Filter: (utc_date > '2002-10-01 00:00:00-07'::timestamp with time zone) Total runtime: 10506.44 msec (3 rows) Which I find surprising and humorous given the popular belief is, mine included, contrary to those results. I can say with pretty high confidence that the patch to use a geometric mean isn't correct after having done real world testing as its break even point is vastly incorrect and only uses an index when there are less than 9,000 rows to fetch, a far cry from the 490K break even I found while testing. What I did find interesting, however, was that it does work better at determining the use of multi-column indexes, but I think that's because the geometric mean pessimizes the value of indexCorrelation, which gets pretty skewed when using a multi-column index. # CREATE INDEX report_user_cat_count_utc_date_user_id_idx ON report_user_cat_count (user_id,utc_date); # CLUSTER report_user_cat_count_utc_date_user_id_idx ON report_user_cat_count; # ANALYZE report_user_cat_count; # SET enable_seqscan = true; SET enable_indexscan = true; SET SET # EXPLAIN ANALYZE SELECT * FROM report_user_cat_count AS rucc WHERE user_id < 1000 AND utc_date > '2002-01-01'::TIMESTAMP WITH TIME ZONE; INFO: cost_seqscan: run_cost: 23685.025000 startup_cost: 0.00 INFO: cost_index: run_cost: 366295.018684 startup_cost: 0.00 indexCorrelation: 0.50 QUERY PLAN Seq Scan on report_user_cat_count rucc (cost=0.00..23685.03 rows=133918 width=64) (actual time=0.28..6100.85 rows=129941 loops=1) Filter: ((user_id < 1000) AND (utc_date > '2002-01-01 00:00:00-08'::timestamp with time zone)) Total runtime: 6649.21 msec (3 rows) # SET enable_seqscan = false; SET enable_indexscan = true; SET SET # EXPLAIN ANALYZE SELECT * FROM report_user_cat_count AS rucc WHERE user_id < 1000 AND utc_date > '2002-01-01'::TIMESTAMP WITH TIME ZONE; INFO: cost_seqscan: run_cost: 23685.025000 startup_cost: 1.00 INFO: cost_ind
