Re: [BUGS] BUG #5055: Invalid page header error
Craig Ringer wrote: > PostgreSQL has to trust the hardware and the OS to do their jobs. If the > OS is, unbeknownst to PostgreSQL, flipping the high bit in any byte Might not even be the OS - it could be the stars (through cosmic rays). http://www.eetimes.com/news/98/1012news/ibm.html '"This clearly indicates that because of cosmic rays, for every 256 Mbytes of memory, you'll get one soft error a month," said Tim Dell, senior design engineer for IBM Microelectronics. ' > The RAID controller might be "helpfully" "fixing" parity errors > in a RAID 5 volume using garbage being returned by a failing disk > during periodic RAID scrubbing. If your raid controller doesn't have ECC memory, and if IBM's right about those soft error stats, it might be doing more harm than good. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Unexpected sort order (suspected bug)
Jeff Davis wrote: > On Mon, 2006-11-27 at 12:44 -0800, Ron Mayer wrote: >> Shouldn't the results of this query shown here been sorted by "b" rather >> than by "a"? >> >> I would have thought since "order by b" is in the outer sql statement it >> would have >> been the one the final result gets ordered by. >> >> li=# select * from (select (random()*10)::int as a, (random()*10)::int as b >> from generate_series(1,10) order by a) as x order by b; >> a | b >> ---+ >> 0 | 8 >> 1 | 10 >> 3 | 4 >> 4 | 8 >> 5 | 1 >> 5 | 9 >> 6 | 4 >> 6 | 5 >> 8 | 4 >> 9 | 0 >> (10 rows) >>... > > It looks like a planner bug. > > Below are two plans; the first fails and the second succeeds. That leads > me to believe it's a planner bug, but what seems strangest to me is that > it does order by a, and not by some new evaluation of (random()*10). > Yeah, looks that way to me too. So how would I report it. Ccing the bugs list? Guess it can't hurt. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[BUGS] Transactions and "create or replace function"
I have a long query something like select slow_function(col) from large_table; and half way through the query, in a separate connection, I CREATE OR REPLACE slow_function I was surprised to see that some of the rows in my select were processed by the old definition and some by the new. I would have expected that since the CREATE OR REPLACE was in a separate connection, and hense a separate transaction, that all the results of the select() will have been processed by the same function. If it matters, it's postgresql 8.0.2; the function was actually a 3-step pl/perl function, where each step uses spi_exec_query() to call the other steps. Right now my test case is large and ugly; but if this is unexpected behavior I'm happy to make a smaller one that I can post here. Ron ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] empty array can crash backend using int_array_enum from contrib.
Using the int_array_enum function from contrib/intagg I can crash the 8.0.2
backend when I pass it an empty array.
fli=# select int_array_enum('{}'::int[]);
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.
!>
fli=# select * from version();
version
-
PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 (SuSE
Linux)
(1 row)
---(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: [BUGS] BUG #1517: SQL interval syntax is accepted by the parser,
Bruce Momjian wrote: This is going to be a backward compatibility problem You say that as if it's a bad thing. In a previous thread, I think Bruce and Tom both commented on ripping out some of the weird undocumented interval behavior: http://archives.postgresql.org/pgsql-patches/2003-09/msg00134.php http://archives.postgresql.org/pgsql-patches/2003-09/msg00123.php There be a lot of dragons in the PostgreSQL interval syntax. Some examples from that old thread: Why is '0.001 years'::interval less than '0.001 months'::interval or While does PostgreSQL think the interval '1Y1M'::interval' means "1 year and one minute, which is confusing because the very similar ISO 8601 time interval 'P1Y1M' means "1 year and one month" to the ISO-8601 spec? At some point I think breaking backward computability for some of the weird undocumented behavior of PostgreSQL's interval syntax would be a good thing. Or perhaps a GUC variable for IntervalStyle kinda like how DateStyle lets you pick ISO or SQL or Postgres styles - but that's ugly in different ways Ron Personally I avoid these problems by still using this hack (a rejected patch http://archives.postgresql.org/pgsql-patches/2003-12/msg00196.php ) that allows ISO 8601 "Time Intervals With Time Unit Designators" in either ISO-8601's "basic format" or "extended format". If anyone's wants the patch for ISO-8601 ( ftp://ftp.qsl.net/pub/g1smd/154N362_.PDF) intervals, let me know and I can send a version ported to 8.X. In my mind ISO-8601 intervals actually make sense while the PostgreSQL intervals and the ISO-SQL intervals are confusing as heck. ---(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: [BUGS] "analyze" putting wrong reltuples in pg_class
On Fri, 2 Aug 2002, Tom Lane wrote:
> >> it would be interesting to see what contrib/pgstattuple shows...
> >> if you can run that conveniently.
> > Gladly, if I'm shown where to find it.
> If you built from a source package, the contrib stuff should be in that
> package. If you used RPMs, look for the pgsql-contrib RPM in the same
> set.
I assume I run it like this... ?
logs2=#
logs2=# select pgstattuple('e_ip_full');
NOTICE: physical length: 293.84MB live tuples: 1697755 (169.26MB, 57.60%)
dead tuples: 0 (0.00MB, 0.00%) free/reusable space: 110.84MB (37.72%)
overhead: 4.67%
pgstattuple
-
0
(1 row)
logs2=#
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] "analyze" putting wrong reltuples in pg_class
On Fri, 2 Aug 2002, Tom Lane wrote: > > Ron Mayer <[EMAIL PROTECTED]> writes: > > On a number of my tables, "analyze" seems to be putting the wrong value of > > "reltuples" in pg_class. "vacuum" seems to be doing the right thing. > > Hmm. analyze by itself generates only an approximate estimate of the > row count (since it only examines a random sample of the rows). But I'd > not have expected it to be off by a factor of 200. Need more info. > What does VACUUM VERBOSE show? logs2=# vacuum verbose e_ip_full; NOTICE: --Relation e_ip_full-- NOTICE: Index e_ip__ip_obsolete_dat: Pages 15905; Tuples 1697755: Deleted 654680. CPU 5.54s/9.96u sec elapsed 183.97 sec. NOTICE: Index e_ip__domain: Pages 15891; Tuples 1697755: Deleted 654680. CPU 10.51s/8.59u sec elapsed 255.68 sec. NOTICE: Removed 654680 tuples in 8324 pages. CPU 7.91s/1.91u sec elapsed 52.01 sec. NOTICE: Pages 37612: Changed 0, Empty 0; Tup 1697755: Vac 654680, Keep 0, UnUsed 454059. Total CPU 42.91s/20.83u sec elapsed 570.05 sec. NOTICE: --Relation pg_toast_110790174-- NOTICE: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0. Total CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM logs2=# > Also, it would be interesting to see what contrib/pgstattuple shows, > if you can run that conveniently. Gladly, if I'm shown where to find it. Google search for pgstattuple shows http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/pgstatuple/ which serves an error page rigth now. > Can you say anything about your typical usage pattern on these tables? > (eg, numbers of inserts vs updates vs deletes) Every few days, 10,000 - 200,000 entries (new IP addresses) are loaded. After loading, an update is run once for each entry (filling in the domain name that goes with the IP address). Then "Vacuum Analyze" is run, and no updates or loads happen until the next large batch. > BTW, it's quite likely that VACUUM FULL will make the problem go away, > so don't do that until we fully understand what's happening ... OK... I have 2 tables that have the same problem, so we can experiment once. :-) Ron ---(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
[BUGS] "analyze" putting wrong reltuples in pg_class
On a number of my tables, "analyze" seems to be putting the wrong value of "reltuples" in pg_class. "vacuum" seems to be doing the right thing. An example of the failure mode is shown below. Please let me know what additional info I could supply if more info would help. Ron logs2=# select count(*) from e_ip_full; count - 1697755 (1 row) logs2=# analyze e_ip_full; logs2=# select relname,reltuples from pg_class where relname = 'e_ip_full'; relname | reltuples ---+--- e_ip_full | 7555 (1 row) logs2=# vacuum e_ip_full; VACUUM logs2=# select relname,reltuples from pg_class where relname = 'e_ip_full'; relname | reltuples ---+- e_ip_full | 1.69776e+06 (1 row) logs2=# analyze verbose e_ip_full; NOTICE: Analyzing e_ip_full ANALYZE logs2=# select relname,reltuples from pg_class where relname = 'e_ip_full'; relname | reltuples ---+--- e_ip_full | 7555 (1 row) logs2=# \d e_ip_full; Table "e_ip_full" Column | Type | Modifiers --+-+--- ip | character varying(16) | dat | date| dom1 | character varying(255) | dom2 | character varying(255) | dom3 | character varying(255) | dom4 | character varying(255) | domn | character varying(1024) | obsolete | boolean | Indexes: e_ip__domain Unique keys: e_ip__ip_obsolete_dat logs2=# ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] Should the optimizer optimize "current_date - interval '1 days'"(fwd)
On a very big table (a data warehouse with >10 million rows), I frequently run queries looking at the past few days. However queries like this: select count(*) from fact where dat > (current_date - interval '1 days'); never uses the index I have on "fact". (Thanks to previous queries it's now ordered by 'dat' so the correlation in pg_stats is '1'.). However if I toss on an extra where clause with a constant like select count(*) from fact where dat > (current_date - interval '1 days') and dat > '2002-05-20'; it hapily uses the index (at least for the second comparison). Should it treat my current_dat... expression as a constant and use the index? Or is there a good reason it doesn't? Thanks, Ron PS: This seems true even if I "set enable_seqscan to off". logs2=# set enable_seqscan to off; logs2=# explain logs2-# select count(*) from fact logs2-# where dat > (current_date - interval '1 days'); NOTICE: QUERY PLAN: Aggregate (cost=101265332.77..101265332.77 rows=1 width=0) -> Seq Scan on fact (cost=1.00..101231544.46 rows=13515325 width=0) logs2=# explain logs2-# select count(*) logs2-# from fact logs2-# where dat > (current_date - interval '1 days') logs2-# and dat > '2002-05-20'; NOTICE: QUERY PLAN: Aggregate (cost=198729.54..198729.54 rows=1 width=0) -> Index Scan using i__fact__dat on fact (cost=0.00..194279.24 rows=1780119 width=0) EXPLAIN logs2=# ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] Inconsistant use of index.
On Wed, 3 Apr 2002, Tom Lane wrote: > > I'm confused. Your examples show the planner correctly estimating the > indexscan as much cheaper than the seqscan. >... > Cut-and-paste mistake here somewhere, perhaps? The plan refers to fact > not fact_by_dat. My apologies... It was indeed doing the right thing on the table that was ordered by date. Sorry for the wasted bandwidth. Ron ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] Inconsistant use of index.
On Tue, 26 Mar 2002, Tom Lane wrote: > Ron Mayer <[EMAIL PROTECTED]> writes: > >> I'm particularly interested in the correlation estimate for the dat > >> column. (Would you happen to have an idea whether the data has been > >> inserted more-or-less in dat order?) > > > I beleve much of February was loaded first, then we back-filled January, > > and daily I've been adding March's results. I don't believe the index-usage > > stopped when we did the january fill... something happend a few days ago after > > a pretty routine daily load. > > The correlation estimate for dat is pretty low (0.086088), which I think > reflects the fact that on a macro level your data is not very orderly > (Feb/Jan/Mar). However, if it's been loaded on a daily basis then the > records for any single day will be together --- which is why the indexed > probe for a single day is so fast. I don't see any way that we can > expect the system to model this effect with only one ordering-correlation > number :-( ... so a proper fix will have to wait for some future release > when we can think about having more extensive stats about ordering. > > In the meantime, it would be interesting to see if re-establishing the > big-picture order correlation would persuade the planner to do the right > thing. Could you do something like this: > > CREATE TABLE foo AS SELECT * FROM fact ORDER BY dat; > TRUNCATE TABLE fact; > INSERT INTO fact SELECT * FROM foo; > DROP TABLE foo; > VACUUM ANALYZE fact; > > (this should leave you in a state where pg_stats shows correlation 1.0 > for fact.dat) and then see what you get from EXPLAIN? > > regards, tom lane I did quite a bit more playing with this, and no matter what the correlation was (1, -0.001), it never seemed to have any effect at all on the execution plan. Should it? With a high correlation the index scan is a much better choice. Ron --- --- create the table with a correlation of "1". --- logs2=# CREATE TABLE fact_by_dat AS SELECT * FROM fact ORDER BY dat; SELECT logs2=# CREATE INDEX fact_by_dat__dat ON fact_by_dat(dat); CREATE logs2=# vacuum analyze fact_by_dat; VACUUM logs2=# select correlation from pg_stats where tablename='fact_by_dat' and attname='dat'; correlation - 1 (1 row) --- --- Still does the "Seq Scan" --- logs2=# explain analyze select count(*) from fact_by_dat where dat='2002-03-01'; NOTICE: QUERY PLAN: Aggregate (cost=380347.31..380347.31 rows=1 width=0) (actual time=77785.14..77785.14 rows=1 loops=1) -> Seq Scan on fact (cost=0.00..379816.25 rows=212423 width=0) (actual time=20486.16..77420.05 rows=180295 loops=1) Total runtime: 77785.28 msec EXPLAIN --- --- Disable Seq Scan... 30 times faster. --- logs2=# set enable_seqscan to off; SET VARIABLE logs2=# explain analyze select count(*) from fact_by_dat where dat='2002-03-01'; NOTICE: QUERY PLAN: Aggregate (cost=5502.57..5502.57 rows=1 width=0) (actual time=2671.20..2671.20 rows=1 loops=1) -> Index Scan using fact_by_dat__dat on fact_by_dat (cost=0.00..4974.99 rows=211036 width=0) (actual time=90.24..2339.64 rows=180295 loops=1) Total runtime: 2693.87 msec ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] Inconsistant use of index.
First off, thanks to everyone on the list who suggested useful workarounds to me - and I wanted to start off by saying that with the workarounds my application is working wonderfully again. Anyway, here's some more information about the "=" vs. "<= and >=" question I had earlier today... On Tue, 26 Mar 2002, Tom Lane wrote: > > Ron Mayer <[EMAIL PROTECTED]> writes: > >> I'm particularly interested in the correlation estimate for the dat > >> column. [...] > > > > [...] > > The correlation estimate for dat is pretty low (0.086088), which I think > reflects the fact that on a macro level your data is not very orderly > [...] > > In the meantime, it would be interesting to see if re-establishing the > big-picture order correlation would persuade the planner to do the right > thing. [...] > (this should leave you in a state where pg_stats shows correlation 1.0 > for fact.dat) and then see what you get from EXPLAIN? Correlation is 1.0, but the optimizer still does not want to use the index. I tried two different extreme attempts one with the optimal ordering suggested above, and one with an exceptionally poor ordering (sorted by time of the day ... so that every day probably appears in every possible block). As expected, pg_stats shows the good ordering has a correlation of "1.0", and the poor ordering has a correlation of "-0.00133352". = logs2=# CREATE TABLE fact_by_dat AS SELECT * FROM fact ORDER BY dat; = SELECT = logs2=# CREATE TABLE fact_by_tim AS SELECT * FROM fact ORDER BY tim; = SELECT = logs2=# CREATE INDEX fact_by_dat__dat ON fact_by_dat(dat); = CREATE = logs2=# CREATE INDEX fact_by_tim__dat ON fact_by_tim(dat); = CREATE = logs2=# vacuum analyze fact_by_dat; = VACUUM = logs2=# vacuum analyze fact_by_tim; = VACUUM = logs2=# explain analyze select count(*) from fact_by_dat where dat='2002-03-01'; = NOTICE: QUERY PLAN: = Aggregate (cost=380347.31..380347.31 rows=1 width=0) (actual time=77785.14..77785.14 rows=1 loops=1) = -> Seq Scan on fact (cost=0.00..379816.25 rows=212423 width=0) (actual time=20486.16..77420.05 rows=180295 loops=1) = Total runtime: 77785.28 msec = EXPLAIN = logs2=# explain analyze select count(*) from fact_by_tim where dat='2002-03-01'; = NOTICE: QUERY PLAN: = Aggregate (cost=380341.09..380341.09 rows=1 width=0) (actual time=79308.22..79308.22 rows=1 loops=1) = -> Seq Scan on fact_by_tim (cost=0.00..379816.25 rows=209934 width=0) (actual time=24.35..78929.68 rows=180295 loops=1) = Total runtime: 79308.35 msec = EXPLAIN = logs2=# select correlation from pg_stats where tablename='fact_by_dat' and attname='dat'; = correlation = - =1 = (1 row) = = logs2=# select correlation from pg_stats where tablename='fact_by_tim' and attname='dat'; = correlation = - = -0.00133352 = (1 row) = In neither case did it use the index. However as shown below, in the case where it was ordered by date the index would have helped a huge amount, while in the case where it was ordered by time using the index hurts a huge amount. = logs2=# set enable_seqscan to off; = SET VARIABLE = logs2=# explain analyze select count(*) from fact_by_dat where dat='2002-03-01'; = NOTICE: QUERY PLAN: = = Aggregate (cost=5502.57..5502.57 rows=1 width=0) (actual time=2671.20..2671.20 rows=1 loops=1) = -> Index Scan using fact_by_dat__dat on fact_by_dat (cost=0.00..4974.99 rows=211036 width=0) (actual time=90.24..2339.64 rows=180295 loops=1) = Total runtime: 2693.87 msec = = EXPLAIN = logs2=# explain analyze select count(*) from fact_by_tim where = dat='2002-03-01'; = NOTICE: QUERY PLAN: = = Aggregate (cost=837849.27..837849.27 rows=1 width=0) (actual time=410705.02..410705.02 rows=1 loops=1) = -> Index Scan using fact_by_tim__dat on fact_by_tim (cost=0.00..837324.43 rows=209934 width=0) (actual time=56.14..410271.50 rows=180295 loops=1) = Total runtime: 410705.17 msec = = EXPLAIN = logs2=# = So with the ideally ordered table the index would have helped by a factor of 30 (2.7 seconds vs. 77 seconds)... but with the bad ordering it hurt by a factor of 5 (411 seconds vs. 79 seconds). Very interesting... Just for my own education, could you bare with me for a few questions from a relative novice... *) Should the optimizer choose a plan that uses the index if the correlation is high enough? *) Instead of the overall correlation across the whole table, would a better metric be the average correlation for data within each page? Then it could recog
Re: [BUGS] Inconsistant use of index.
On Tue, 26 Mar 2002, Tom Lane wrote:
>
> Ron Mayer <[EMAIL PROTECTED]> writes:
> > [...] pretty large, PostgreSQL suddenly stopped using indexes [...]
> [...]
>
> 212K estimate for 180K real is not bad at all. So the problem is in the
> cost models not the initial row count estimation.
>
> If you force an indexscan via "set enable_seqscan to off", what does
> EXPLAIN ANALYZE report?
It then uses the index:
===
== logs2=# set enable_seqscan to off;
== SET VARIABLE
== logs2=# explain analyze select count(*) from fact where dat='2002-03-01';
==
== NOTICE: QUERY PLAN:
==
== Aggregate (cost=840488.03..840488.03 rows=1 width=0) (actual
== time=2753.82..2753.82 rows=1 loops=1)
== -> Index Scan using i_fact__dat on fact (cost=0.00..839957.59 rows=212174
== width=0) (actual time=101.25..2434.00 rows=180295 loops=1)
== Total runtime: 2754.24 msec
===
> Also, what do you get from
> select * from pg_stats where tablename = 'fact';
> I'm particularly interested in the correlation estimate for the dat
> column. (Would you happen to have an idea whether the data has been
> inserted more-or-less in dat order?)
I've attached that output as an attachment.
I beleve much of February was loaded first, then we back-filled January,
and daily I've been adding March's results. I don't believe the index-usage
stopped when we did the january fill... something happend a few days ago after
a pretty routine daily load.
Oh... one more interesting thing...
There are a couple big exceptions to the even distribution of data.
Almost every day has between 19 and 27 records except '2002-03-08'
which has 404293 records and '2002-03-25' which has 6 records.
For that particular day, the "<= ... >=" trick doesn't work either.
===
==logs2=# explain select count(*) from fact where dat<='2002-03-08' and
==dat>='2002-03-08';
==NOTICE: QUERY PLAN:
==
==Aggregate (cost=422125.92..422125.92 rows=1 width=0)
== -> Seq Scan on fact (cost=0.00..421128.67 rows=398900 width=0)
==
==EXPLAIN
==logs2=#
==logs2=# explain select count(*) from fact where dat<='2002-03-07' and
==dat>='2002-03-07';
==NOTICE: QUERY PLAN:
==
==Aggregate (cost=6.00..6.00 rows=1 width=0)
== -> Index Scan using i_fact__dat on fact (cost=0.00..5.99 rows=1 width=0)
==
==EXPLAIN
===
I also believe that may have been the day when the index stopped
working for "=" for all dates.
Ron
tablename | attname | null_frac | avg_width | n_distinct |
most_common_vals |
\
most_common_freqs
| histogram_bounds
\
| correlation
---+-+---+---++-+\
---+-\
---+-
fact | dat | 0 | 4 | 83 |
{2002-03-08,2002-03-09,2002-01-05,2002-01-18,2002-02-04,2002-03-24,2002-03-23,2002-02-16}
| {0.0216667,0.0186667,0.0163\
333,0.016,0.016,0.016,0.0156667,0.015}
|
{2002-01-01,2002-01-09,2002-01-16,2002-01-25,2002-02-01,2002-02-09,2002-02-17,2002-0\
2-24,2002-03-05,2002-03-15,2002-03-22} |0.086088
fact | tim | 0 | 8 | 226609 |
{00:30:24,03:07:10,04:04:29,04:17:39,05:08:28,05:14:47,05:15:45,05:20:08,05:36:23,05:59:09}
| {0.00067,0.00067,0.\
00067,0.00067,0.00067,0.00067,0.00067,0.00067,0.00067,0.00067}
|
{00:01:35,03:15:29,05:57:26,08:03:40,09:52:10,11:40:56,13:25:21,15:34:16,17:59:13,20\
:35:18,23:58:42} | -0.00532619
fact | ip_id | 0 | 4 | 217853 |
{10068,12843,1773838,6047,12844,23567,24900,484794,5637,9246}
| {0.01,0.0027,0.0023\
,0.0017,0.0017,0.0017,0.0017,0.0017,0.0013,0.0013}
| {38,20463,91088,236641,430973,634542,842818,1091776,1326722,1566578,1840292}
\
[BUGS] Inconsistant use of index.
In porting a pretty large (10s of millions of records) data warehouse from Oracle to PostgreSQL, Once some of my tables started getting pretty large, PostgreSQL suddenly stopped using indexes when I use expressions like "col = value" decreasing performance by 20X. This meant that my daily reports started taking two days instead of 2 hours to run). Interestingly when I re-write the queries using >= and <= to produce identical results, the index works fine. Example queries in question include: select count(*) from fact where dat='2002-03-01'; select count(*) from fact where dat<='2002-03-01' and dat>='2002-03-01'; The distribution of values in "dat" are roughly evenly spaced from '2002-01-01' through '2002-03-25'. Attached below are A: Information about the table, including "\d" and "vacuum verbose analyze" B: Output of "explain analyze" from the above queries (showing the 20X slowdown) C: Version and configuration information. Any suggestions on what I should look at next would be appreciated. Thanks much, Ron PS: As a quite perverse workaround, I rewrote all my queries to have "col<=val and col>=val" everywhere I used to have "col=val" and everything is running fine again... but that's just wierd. == A: Information about the table logs2=# \d fact Table "fact" Column | Type | Modifiers ++--- dat| date | tim| time without time zone | ip_id | integer| bid_id | integer| req_id | integer| ref_id | integer| Indexes: i_fact__bid_id, i_fact__dat, i_fact__ref_id, i_fact__req_id, i_fact__tim logs2=# select count(*) from fact; count -- 18410778 (1 row) logs2=# vacuum verbose analyze fact; NOTICE: --Relation fact-- NOTICE: Pages 144967: Changed 0, Empty 0; Tup 18410778: Vac 0, Keep 0, UnUsed 0. Total CPU 11.56s/2.97u sec elapsed 71.91 sec. NOTICE: Analyzing fact VACUUM == B: Explain Analyze for the two queries. == Note that the <=, >= one was over 20X faster. logs2=# explain analyze select count(*) from fact where dat='2002-03-01'; NOTICE: QUERY PLAN: Aggregate (cost=375631.14..375631.14 rows=1 width=0) (actual time=76689.42..76689.42 rows=1 loops=1) -> Seq Scan on fact (cost=0.00..375101.72 rows=211765 width=0) (actual time=20330.96..76391.94 rows=180\ 295 loops=1) Total runtime: 76707.92 msec EXPLAIN logs2=# explain analyze select count(*) from fact where dat<='2002-03-01' and dat >='2002-03-01'; NOTICE: QUERY PLAN: Aggregate (cost=5.98..5.98 rows=1 width=0) (actual time=2921.39..2921.39 rows=1 loops=1) -> Index Scan using i_fact__dat on fact (cost=0.00..5.98 rows=1 width=0) (actual time=73.55..2583.53 ro\ ws=180295 loops=1) Total runtime: 2921.55 msec EXPLAIN logs2=# == C: Version and configuration information. [17]localhost:~/apps/pgsql% psql -V psql (PostgreSQL) 7.2 contains support for: readline, history Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group Portions Copyright (c) 1996, Regents of the University of California Read the file COPYRIGHT or use the command \copyright to see the usage and distribution terms. [17]localhost:/scratch/pgsql/data% diff -wiu postgresql.conf postgresql.conf.bak --- postgresql.conf Sat Mar 23 15:39:34 2002 +++ postgresql.conf.bak Tue Mar 5 19:33:54 2002 @@ -50,7 +50,7 @@ #shared_buffers = 1# 2*max_connections, min 16 ## goes to about 84 meg with 4000. #shared_buffers = 4000# 2*max_connections, min 16 -shared_buffers = 1# 2*max_connections, min 16 +shared_buffers = 8000# 2*max_connections, min 16 #max_fsm_relations = 100# min 10, fsm is free space map #max_fsm_pages = 1 # min 1000, fsm is free space map -- Ronald Mayer Director of Web Business InterVideo, Inc. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
