[BUGS] Bug #628: move works incorrectly on cursors using GiST indexes
Dmitry Tkach ([EMAIL PROTECTED]) reports a bug with a severity of 2 The lower the number the more severe it is. Short Description move works incorrectly on cursors using GiST indexes Long Description If you declare a cursor for a query, that's using a gist index, then fetch a few rows from it, and then move it backwards the same number of rows, and fetch again, the output starts with the second row, not the first one as expected. I am using btree_gist from contrib/ below as an example of a gist implementation, but note that the problem is not specific to btree_gist - I ran into it with another (custom) implementation, and then used btree_gist to verify it wasn't something I did wrong with my implementation, and I got the same problem. So, it looks like the problem is with the gist itself, not with any particular extension. Sample Code \i contrib/btree_gist/btree_gist.sql create table test (x int); insert into x values (1); insert into x values (2); insert into x values (3); begin; declare test_cursor for select * from x where x > 1; fetch 1 from test_cursor; x --- 2 (1 row) move -1 in test_cursor; MOVE 0 fetch 1 from test_cursor; x --- 2 (1 row) commit; -- Works as expected so far... Now - THE PROBLEM: create index test_idx on test using gist (x gist_int4_ops); set enable_seqscan = false; begin; declare test_cursor for select * from x where x > 1; fetch 1 from test_cursor; x --- 2 (1 row) move -1 in test_cursor; MOVE 0 fetch 1 from test_cursor; x --- 3 < HERE IT IS: it is supposed to return 2!!! move -1 in test_cursor; MOVE 1 fetch 1 from test_cursor; x --- 3 < Now it works - the problem is only with refetching the FIRST Note, that with a regular (btree) index it works ok, as it does with the sequentual scan... No file was uploaded with this report ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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] date function 'age' problem
> osk=> select age('2001-03-29','2001-03-01');
> ---
> 27 days 23:00
> osk=> select age('2002-03-29','2002-03-01');
> ---
> 1 mon
> osk=> select version();
> PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.4
...
> look at the difference betwen results of execution function 'age' for the
> same time interval in year 2001 and 2002. why there is '1 mon' when it
> should be '27 days'.
You may be calculating an interval across daylight savings time
boundaries, which may have been done without accounting for time zones
in the version you are running. I'm not able to reproduce the problem on
my 7.2 installation:
thomas=# set time zone 'pst8pdt';
SET VARIABLE
thomas=# select age('2001-03-29','2001-03-01');
-
28 days
thomas=# select age('2002-03-29','2002-03-01');
-
28 days
thomas=# select version();
---
PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.96
May be time to upgrade...
- Thomas
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] Inconsistant use of index.
Ron Mayer <[EMAIL PROTECTED]> writes: > 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. I'm confused. Your examples show the planner correctly estimating the indexscan as much cheaper than the seqscan. > 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 Cut-and-paste mistake here somewhere, perhaps? The plan refers to fact not fact_by_dat. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] Problem in postgresql installation
Hi I have installed a 7.1.3 postgresql version on my linux server. When I type "createuser " it asks me : Shall the new user allowed to create databases (y/n)? Shall the new user allowed to create more new user (y/n)? I answer yes to the both questions and then a bug report said : " psql : connetDbstart() -- connect() failed - no such file or directory Is the postmaster running locally and accepting connections on Unix socket :'/tmp/.s.PSQL.5432' createuser : creation of user 'xxx' failed. Thanks for your help. Mr Paquin
[BUGS] pls unsubscribe me
please , unsubscribe me from list i dont want to receive nay mails reg : postgresql __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
