Attn PG gurus / coders : New approach for ORDER BY ? (was: Re: Show stopper ?)
От | Jan Vicherek |
---|---|
Тема | Attn PG gurus / coders : New approach for ORDER BY ? (was: Re: Show stopper ?) |
Дата | |
Msg-id | Pine.LNX.3.96.980123213848.497I-100000@ann.ied.com обсуждение исходный текст |
Ответ на | Re: [HACKERS] Show stopper ? (was: Re: "cruising" or "browsing" through tables using an index / ordering) (The Hermit Hacker <scrappy@hub.org>) |
Ответы |
Re: [QUESTIONS] Attn PG gurus / coders : New approach for ORDER BY ? (was: Re: Show stopper ?)
("Ron O'Hara" <rono@pen.sentuny.com.au>)
Re: Attn PG gurus / coders : New approach for ORDER BY ? (was: Re: Show stopper ?) (The Hermit Hacker <scrappy@hub.org>) |
Список | pgsql-hackers |
On Fri, 23 Jan 1998, The Hermit Hacker wrote: > On Fri, 23 Jan 1998, Jan Vicherek wrote: > > > On Fri, 23 Jan 1998, The Hermit Hacker wrote: > > > > > I'm curious, but can the "Big (commercial) Boys" do this? If so, > > > can you please provide an example of which and how? > > > > Hmm, well, the one we are switching from does this ;-) (Informix 3.3 > > ALL-II C interface). It's not SQL, tho. > > Okay, well, ummm...now you are comparing apples to oranges > then...if you wanted a non-SQL engine to replace Informix, PostgreSQL > isn't what you are looking for :( Hmm, I thought that PostgreSQL would have two interfaces : an SQL interface (doing all the optimizing, joining and the complicated stuff) and a low(er)-level C/C++/whatever interface which would allow to use less of the intelligence of PostgreSQL and more of it's retireval system, including indexing. > > > all I've seen is ... and several examples on how to accomplish it using > > > PostgreSQL, but that's it... > > > > Wait, have you seen here an example that accomplishes this which > > wouldn't need the whole table copied and wouldn't lock the table against > > updates ? > > First off, *nothing* you are going to be able to accomplish in > *any* SQL engine is going to prevent locking the table against > updates... Sorry, I wasn't precise again : I mean I can't stop other people from updating a table just because one guy is out there "browsing" through it. > > > Why not put a sequence field on the table so that you can do: > > > select * from table where rowid = n; -or- > > > select * from table where rowid = n - 1; -or- > > > select * from table where rowid = n + 1; -or- > > > select * from table where rowid >= n and rowid <= n+x; > > > > > > And create the index on rowid? > > > > Because I also need to be able to INSERT rows. That would require > > renumeration of half the table (remember, it's 40MB, 400,000 rows) every > > time I do an INSERT. > > Okay, you are confusing INSERT then...INSERT in SQL just adds a > row, that's it...it doesn't perform any "sorting" on it...that's what the > ORDER BY command does... The INSERT causes all indices on that table to be updated. If I used a field in the table to serve as a pseudo-index (user-level index, not system-level index), *then* that would require "manual" renumeration of rowid field in half the table every time I do an INSERT. > ...but, I now understand what *you* mean by INSERT... good :) > > I *still* think that there *has to* be a way to find a value that is > > immediatelly next to one I have. This seems like such a primitive > > operation. Even the backend must be doing it on some operations, it would > > seem. > > Not possible... Remeber that I have an index on the field that I'm looking for the "next" value. Is it not possible in such case ? Even by calling some lower-level C function to give me the next value ? > INSERT into a table doesn't "merge" the record > between its lower/higher bounds...it just adds it to the very end of the > table. And an index doesn't "sort" the data either...that is what the > ORDER BY clause is for... I guess I wasn't precise enough. I was aware of these two facts when I was writing the previous msgs. Index doesn't "sort". Index gets sorted. But pseudo-index doesn't even get sorted, I would have to sort it "manually" after every INSERT I would make. > > Maybe even in SQL. Maybe something like (I'm not an SQL expert) : "SELECT > > IndexField from MyTable where InxdexField > 'my_current_value' and > > IndexField < ("all IndexFields that are bigger than the IndexField > > searched for") > > From your sample above, is your first SQL call going to pull out > all 40MB of data in one select statement, then your second 40MB minus the > first X records? Hmm, I could have hoped that the optimizer would recognize what I'm trying to do and would only pull out that one record, but I guess not. Never mind this attempt then. > What you want to do is: > > begin > declare mycursor cursor for select * from pg-user order by <somefield>; > move $forward in FOO; > fetch $retrieve in FOO; > close foo; > end; (I guess you meant "close mycursor", not "close foo".) I've just tried this on half-the-size table, and it took 70 seconds :-( meray=> begin; BEGIN meray=> declare mycursor cursor for select * from master order by mpartno; SELECT meray=> fetch forward 1 in mycursor; close mycursor; end; mpartno |mlistprc|mwarcomp|mcost|msupercd|mbasecd|mflag|mpackqty|mdescrip -------------------+--------+--------+-----+--------+-------+-----+--------+---------------- F 0 AVZK | 581| 418| 311|S | | 6| 0|SPARK PLUG (1 row) meray=> close mycursor; CLOSE meray=> end; END meray=> \d masterind Table = masterind +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | mpartno | (bp)char | 19 | +----------------------------------+----------------------------------+-------+ Since the ORDER BY field (mpartno) has an index on it, why wouldn't Postgres just take that index and go with it one-by-one ?! Such approach wouldn't take 70 seconds on idle P200+ 64MB, but would take 1/2 a second. Is such approach pricipally/fundamentally wrong, or is this correct approach, and postgres just needs some more code written for this thing to work ? (I.e. would other things break if this was implemented ?) -- whereas single select (which uses index) takes half a second. meray=> select * from master where mpartno = 'F 0 AVZK '; mpartno |mlistprc|mwarcomp|mcost|msupercd|mbasecd|mflag|mpackqty|mdescrip -------------------+--------+--------+-----+--------+-------+-----+--------+---------------- F 0 AVZK | 581| 418| 311|S | | 6| 0|SPARK PLUG F 0 AVZK | 581| 418| 311|S | | 6| 0|SPARK PLUG (2 rows) meray=> Now I want do see the row that is in the masterind after the first, in this case it contains the same data. If we can get "this new approach" to work, > Basically, take your table, move to the $forward record in it, > grab the next $retrieve records and then release the table. But this creates 70-second lock on the table every time I want to see next record :-( > > Important : I'm not looking for a "pure SQL" solution. I'm writing a C > > emulation library, so if it can be achieved via a call to a C Postgres > > function, it would be great. > > You'd be better off looking at something like GDBM (which, by the > way, also creates a lock against updates while another is reading the > database)...unless I'm missing something, you aren't looking at doing > anything that *requires* an SQL engine :( This looks like a sad suggestion to me. I'll kick and scream just that I wouldn't have to go with Velocis RAIMA's lower-level C interface. (I'm just staring into its manual to figure out the sequence of C calls I would have to make to accomplish this.) Thanx to all, Jan -- Gospel of Jesus is the saving power of God for all who believe -- Jan Vicherek ## To some, nothing is impossible. ## www.ied.com/~honza >>> Free Software Union President ... www.fslu.org <<< Interactive Electronic Design Inc. -#- PGP: finger honza@ied.com
В списке pgsql-hackers по дате отправления:
Предыдущее
От: The Hermit HackerДата:
Сообщение: Re: [HACKERS] Show stopper ? (was: Re: "cruising" or "browsing" through tables using an index / ordering)
Следующее
От: Bruce MomjianДата:
Сообщение: Re: [QUESTIONS] Re: [HACKERS] Show stopper ? (was: Re: "cruising" or "browsing" through tables using an index / ordering)