[SQL] Dump db with LO
Hello. I wonder if someone could give me a tip how should I dump db with LO. I use pg_dump and pg_dumpall and evrything is dumped but not LO. What should I do with that. I will be very greatful fot answer. P.S. Sorry for my english ;) Mateusz Mazur [EMAIL PROTECTED] POLAND ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] queries and inserts
Removing indexes will speed up the INSERT portion but slow down the SELECT portion. Just an FYI, you can INSERT into table (select whatever from another table) -- you could probably do what you need in a single query (but would also probably still have the speed problem). Have you EXPLAINed the SELECT query to see if index scans are being used where possible? -Mitch - Original Message - From: "Rini Dutta" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, August 25, 2000 12:20 PM Subject: [SQL] queries and inserts > Hi, > > I am interested in how to speed up storage. About 1000 > or more inserts may need to be performed at a time , > and before each insert I need to look up its key from > the reference table. So each insert is actually a > query followed by an insert. > > The tables concerned are : > CREATE TABLE referencetable(idx serial, rcol1 int4 NOT > NULL, rcol2 int4 NOT NULL, rcol3 varchar(20) NOT > NULL, rcol4 varchar(20), PRIMARY KEY(idx) ... > CREATE INDEX index_referencetable on > referencetable(rcol1, rcol2, rcol3, rcol4); > > CREATE TABLE datatable ( ref_idx int4, > start_date_offset int4 NOT NULL, stop_date_offset int4 > NOT NULL, dcol4 float NOT NULL, dcol5 float NOT NULL, > PRIMARY KEY(ref_idx, start_date_offset), CONSTRAINT c1 > FOREIGN KEY(ref_idx) REFERENCES referencetable(idx) ); > > I need to do the following sequence n number of times > - > 1. select idx (as key) from referencetable where > col1=c1 and col2=c2 and col3=c3 and col4=c4; (Would an > initial 'select into temptable' help here since for a > large number of these queries 'c1' and 'c2' > comnbinations would remain constant ?) > 2. insert into datatable values(key, ); > > I am using JDBC interface of postgresql-7.0.2 on > Linux. 'referencetable' has about 1000 records, it can > keep growing. 'datatable' has about 3 million records, > it would grow at a very fast rate. Storing 2000 > records takes around 75 seconds after I vacuum > analyze. (before that it took around 40 seconds - ???) > . I am performing all the inserts ( including the > lookup) as one transaction. > > Thanks, > Rini > > > __ > Do You Yahoo!? > Yahoo! Mail - Free email you can access from anywhere! > http://mail.yahoo.com/ >
[SQL] Weighted Searching
I emailed the list a while back about doing some weighted searching, asking if anyone had implemented any kind of weighted search in PostgreSQL.. I'm still wondering the same thing and if anyone has, I would greatly appreciate a private email, I'd like to discuss it in detail.. I have several ideas but most of them are pretty dirty and slow.. What I need to do is allow the user to assign weights to fields and then specify a minimum weight which would dictate results.. Example : A search on two fields, degree and years_experience, location_state. The user assigns degree a weight of 10, years_experience a weight of 10 and location_state a weight of 10. Then specifies the minimum weight as 20, meaning that any results returned would have to have at least two of the fields an exact match (any two that where the sum of the weight equals 20). This could be carried out to many, many fields and extremely high weights.. The problem I'm having is figuring out a good way to assign the weights to individual fields and test to see if an individual field is exactly matched in the query (without running a single query for each field searched on. Example: The SQL query for the search above might be : SELECT * FROM people WHERE degree='MBA' and years_experience='5' and location_state='Arizona' I would want people that have an MBA and 5 years experience but they wouldn't necessarily have to be in Arizona (because our minimum weight is 20, only two would have to match).. Hopefully I'm not over-explaining to the point of confusion.. If anyone would have any ideas, please drop me an email.. Thanks!!! -Mitch
Re: [SQL] sql query not using indexes
I'm curious, I know PG doesn't have support for 'full' text indexing so I'm wondering at what point does indexing become ineffective with text type fields? -Mitch - Original Message - From: "Stephan Szabo" <[EMAIL PROTECTED]> To: "User Lenzi" <[EMAIL PROTECTED]> Cc: "pgsql-sql" <[EMAIL PROTECTED]> Sent: Wednesday, September 20, 2000 11:23 AM Subject: Re: [SQL] sql query not using indexes > On Wed, 20 Sep 2000, User Lenzi wrote: > > > if I start a query: > > > > explain select * from teste where login = 'xxx' > > results: > > Index Scan using teste1 on teste (cost=0.00..97.88 rows=25 ) > > > > > > however a query: > > explain select * from teste where login > 'AAA' > > results: > > Seq Scan on teste > > > > > > On a machine running version 6.5 both queries results index scan. > > > > this results that the version 6.5 is faster than version 7.0.2 on this > > kind of > > query. > > > > > > Any explanation??? > > Have you done a vacuum analyze on the table? Also, what does the row > count for the second query look like? It's probably deciding that > there are too many rows that will match login >'AAA' for index scan > to be cost effective. So, actually, also, what does > select count(*) from teste where login>'AAA" give you on the 7.0.2 box. > > >
Re: [SQL] Multiple Index's
> Hello all, > > How would I prevent a user from submitting information to a table once they > have already done so for that day. The best you could probably do is to go back and delete undesired recoords at the end of the day because if it is as you said, they've already put the information into the database. > I would need them to be able > information on future dates as well as have information in the table from > past dates from that user. Not positive what you mean here but just use a date (or timestamp) column in the table to indicate when the record was added. > I am looking for something like insert user_id, date, info where user_id > and date are not the same... does that make sense? Nope, it doesn't -- at least to me :-) How about some table structures and some more information, I'm just not exactly sure what you'd like to do.. -Mitch
Re: [SQL] OID Perfomance - another question
Aren't there a pretty big concerns when using OIDs as IDs to relate records in different tables to each other? Wouldn't the OIDs be totally re-assigned if you had to dump/restore your database? Just a question to satisfy my own curiosity, thanks! -Mitch > Folks, > > Because it's a very elegant solution to my database structure issues, > I'm using OID's extensively as referents and foriegn keys. However, I > wanted to see if others had previous experience in this (answer as many > as you like): > > 1. Is there a performance loss on searches and joins when I use the OID > as a liniking field as opposed to a SERIAL column?
Re: [SQL] OID Perfomance - Object-Relational databases
Aren't OIDs just integers? Isn't this limit just the limit of the value an int4 can hold? 2,147,483,647 is the max for an int4 (I think) so at 500 million a day you're looking at more like 4.29 (and change) days If I'm correct in all the above, there wouldn't be any way to increase the limit without the server running on a 64-bit machine (which you could do, I guess).. *shrug* just some thoughts.. -Mitch - Original Message - From: "Michael Ansley" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; "sqllist" <[EMAIL PROTECTED]> Cc: "Jeff MacDonald" <[EMAIL PROTECTED]> Sent: Tuesday, October 03, 2000 9:17 AM Subject: RE: [SQL] OID Perfomance - Object-Relational databases > Hi, Josh, > > In fact, the last point about OIDs is particularly pertinent, because we are > expected to process up to 500 million records daily, thus exhausting the > limit in, um, eight days. > > Is anybody aware of when this limit will be raised. > > Cheers... > > >> -Original Message- > >> From: Josh Berkus [mailto:[EMAIL PROTECTED]] > >> Sent: 03 October 2000 17:06 > >> To: sqllist > >> Cc: Jeff MacDonald > >> Subject: [SQL] OID Perfomance - Object-Relational databases > >> > >> > >> Folks, > >> > >> Because it's a very elegant solution to my database > >> structure issues, > >> I'm using OID's extensively as referents and foriegn keys. > >>However, I > >> wanted to see if others had previous experience in this > >> (answer as many > >> as you like): > >> > >> 1. Is there a performance loss on searches and joins when > >> I use the OID > >> as a liniking field as opposed to a SERIAL column? > >> > >> 2. Can I define my own index on the OIDs of a table? > >> > >> 3. What is the difference between these two DDL statements > >> in terms of > >> data access and PG-SQL performance (assuming that table clients has > >> already been defined): > >> > >> CREATE TABLE client_addresses AS ( > >> client_OID OID REFERENCES clients, > >> address1 VARCHAR (30), > >> address2 VARCHAR (30), > >> address3 VARCHAR (30) > >> ) > >> and: > >> CREATE TABLE client_addresses AS ( > >> client clients, > >> address1 VARCHAR (30), > >> address2 VARCHAR (30), > >> address3 VARCHAR (30) > >> ) > >> > >> (This is Michael's questions rephrased) > >> > >> 4. Int4 seems kinda small to me for a value that needs to enumerate > >> every single database object. Within a couple of years of > >> heavy use, a > >> customer-transaction database could easily exceed 2 billion objects > >> created (and destroyed). Are there plans to expand this to Int8? > >> > >> -Josh Berkus > >> > >> P.S. My aplolgies if I've already posted these questions; I never > >> received them back from the list mailer. > >> > >> > >> > >> -- > >> __AGLIO DATABASE SOLUTIONS___ > >> Josh Berkus > >> Complete information technology [EMAIL PROTECTED] > >> and data management solutions (415) 436-9166 > >> for law firms, small businesses fax 436-0137 > >> and non-profit organizations. pager 338-4078 > >> San Francisco > >> >
[GENERAL] Re: How to simulate MEMO data type?Thanks!
Use the unlimited length PostgreSQL type "text" (In 7.1 it's unlimited, before there were limits). -Mitch - Original Message - From: "Maurizio Ortolan" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Sunday, April 15, 2001 1:18 PM Subject: How to simulate MEMO data type?Thanks! > Hello! > > I'm porting a database from MS Access > to PostgreSQL. > > How can I simulate in pgsql the 'MEMO' > (up to 65000 chars) data type, which > is often used in Access ? > > Where can I find an easy example? > > I don't' know if it's important, but > I'm using Linux,Apache,Javascript & > PHP. > ^^^ > > Many thanks to all of you! > CIAO! > MAURIZIO > > > *** > ** Happy surfing on THE NET !! ** > ** Ciao by ** > ** C R I X 98 ** > *** > AntiSpam: rimuovere il trattino basso > dall'indirizzo per scrivermi... > (delete the underscore from the e-mail address to reply) > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > ---(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
[SQL] Quick contraint question
I have a table that has a frild called ID. I will be inserting data into that field that will either be a unique number or blank, Is there a way to do this either at table creation time or by using some check() voodoo? Thanks. -- Vincent Stoessel Linux Systems Developer vincent xaymaca.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] staggered query?
hi! im new to SQL, and i need to find a solution to this problem: i have a table with two columns, the first column is of type timestamp. the table contains hundreds of thousands of records. i need to get all the entries/records at every 10 seconds interval. example, given a table: hh/mm/ss | data --- 00:00:00 1 00:00:01 2 00:00:02 3 00:00:03 4 00:00:04 5 00:00:05 6 00:00:06 7 00:00:07 8 .. .. my query should return: 00:00:10 00:00:20 00:00:30 (etc) is this possible? if yes, how do i do it? thanks! --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.484 / Virus Database: 282 - Release Date: 5/27/2003 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] staggered query?
hey thanks! it worked:) here's how we did it: select sampletime from data where (extract(seconds from sampletime)::int)::text in (14, 17, 19); -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Denis P Gohel Sent: Wednesday, April 21, 2004 3:12 PM To: [EMAIL PROTECTED] Subject: Re: [SQL] staggered query? Hi Try this.. SELECT Col1 , Col2 FROM yourtable WHERE to_number(to_char(col1, 'SS'),'99') / 10 ) in (10,20,30,40,50,00); HTH Denis > - Original Message - > From: Vincent Ladlad <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Wednesday, April 21, 2004 8:23 AM > Subject: [SQL] staggered query? > > > > hi! im new to SQL, and i need to find a solution > > to this problem: > > > > i have a table with two columns, the first column > > is of type timestamp. > > > > the table contains hundreds of thousands of records. > > i need to get all the entries/records at every 10 seconds interval. > > example, given a table: > > > > hh/mm/ss | data > > --- > > 00:00:00 1 > > 00:00:01 2 > > 00:00:02 3 > > 00:00:03 4 > > 00:00:04 5 > > 00:00:05 6 > > 00:00:06 7 > > 00:00:07 8 > > .. > > .. > > > > my query should return: > > 00:00:10 > > 00:00:20 > > 00:00:30 > > (etc) > > > > is this possible? if yes, how do i do it? > > > > thanks! > > > > --- > > Outgoing mail is certified Virus Free. > > Checked by AVG anti-virus system (http://www.grisoft.com). > > Version: 6.0.484 / Virus Database: 282 - Release Date: 5/27/2003 > > > > > > > > ---(end of > > broadcast)--- > > TIP 4: Don't 'kill -9' the postmaster > > ---(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 --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.484 / Virus Database: 282 - Release Date: 5/27/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.484 / Virus Database: 282 - Release Date: 5/27/2003 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Migration from SQLite Help (Left Join)
SELECT c.customer_id as customer_id,c.customer_number as customer_number,
c.customer_name as customer_name,c.customer_status as
customer_status,cat.category_name as category_name,
c.bill_state as bill_state, coalesce(ctots.balance_due, 0.00) as
balance FROM customers as c,
customer_categories as cat
left join
(Select customer_id as cid, coalesce(sum(balance_due),0) as balance_due
FROM invoice_master WHERE status = 'Pending' group by cid) ctots on
ctots.cid = c.customer_id
where cat.category_id = c.category_id AND customer_name
LIKE lower('%%') AND (c.customer_status = 'Terminated' OR
c.customer_status = 'Active' or c.customer_status = 'Inactive')
ORDER BY c.customer_number DESC LIMIT 25
I know, it's a beast, but I'm porting an application from SQLite to
PostgreSQL and this is the only query that isn't working properly in
PostgreSQL.
The error is "ERROR: invalid reference to FROM-clause entry for table "c"
Hint: There is an entry for table "c", but it cannot be referenced
from this part of the query.
Character: 475" - it's the "on ctots.cid = c.customer_id " part that's breaking.
Is there any way to accomplish the same thing in PG?
--
- Mitchell Vincent
- K Software - Innovative Software Solutions
- Visit our website and check out our great software!
- http://www.ksoftware.net
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
[SQL] enumerate groups given a certain value
Hello,
Here is a sql problem, which I thought simple at first, but for which I
ended up with a solution I find surprisingly complicated.
I really think I could have achieved a much easier way of handling this,
but I do not manage to find the trick allowing a very simple and
efficient query to solve the problem.
Let's present it with a sample case. The initial table is the following
one :
drop table if exists test_gen ;
create table
test_gen as
select *
from (
select
chr((round(random()* 25) +65)::int) as id
, random()* 100 as val
from
generate_series(1,200) as g
order
by id
) as foo
select * from test_gen;
---
What I want to do is to enumerate lines for each group of id, following
the order of val.
For example :
id val gen
A 2.651051385328171
A 38.92893604934222
A 74.60891641676433
B 2.015121886506681
B 11.46420473232872
B 31.26432197168473
B 65.84279797971254
C 0.759994331747293 1
C 11.89057962037622
C 13.73886489309373
C 49.19343511573974
C 83.18619034253065
D 45.82689679227771
D 57.11615891195832
E 9.721256978809831
E 61.324825277552 2
E 70.33489583991473
F 0.498912342342371
Here is the solution I ended up with :
---
-- first count number of ids per group
drop table test_gen2 ;
create table test_gen2 as
select t1.*, t2.nb
from
test_gen as t1,
(
SELECT
id, count(*) as nb
FROM
test_gen
GROUP BY id
) as t2
WHERE
t1.id =t2.id
ORDER BY
t1.id;
create sequence seq_test_gen start with 1;
create sequence seq_test_gen2 start with 1;
-- get the table with the order set (gen is our order)
select
*
from
(
select
foo1.*,
nextval('seq_test_gen') as serial
from (
select
*
from
test_gen2
order by
id, val
) as foo1
) as t1,
(
select
foo.*,
nextval('seq_test_gen2') as serial
from (
select
gb1.*,
generate_series(1, gb1.nb) as gen
from (
select
id, nb
from
test_gen2
group by
id, nb
) as gb1
order by
gb1.id, gen
) as foo
) as t2
where
t1.serial = t2.serial
;
---
The problem seems to be as easy as : . But I could not find a better way to do that than putting a
serial on left and right side and do a join on this serial.
I also tried to find a solution using a modulo but could not manage to
get it work.
Anybody for a ray of light on a different approach ? This look like a
recurrent problem, isn't there an experienced sql programmer here who
tackled this issued a couple of time ?
Thanks for any help,
Vincent
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] date() indexing error..
Using PostgreSQL 7.0 I'm Doing ... ipa2=# CREATE INDEX "app_stat_month" on applicant_stats(date(month)); ERROR: SQL-language function not supported in this context. ipa2=# \d applicant_stats Table "applicant_stats" Attribute | Type| Modifier ---+---+-- app_id| integer | month | date | user_id | integer | view_time | timestamp | Index: app_id When I try and do the above there is one record in the database. ipa2=# select * from applicant_stats; app_id | month| user_id | view_time ++-+-- 56941 | 05-26-2000 | 291 | Fri May 26 09:19:41 2000 EDT (1 row) If I erase it I get the same error, only when I try and insert data into the table.. I'm doing these kind of indexes on several tables, this is the only one I'm having this occur.. Any ideas? Thanks! -Mitch
Re: [SQL] does the' text' type cann't store more than 20,000char ?
It can't be any larger than 8k (minus a bit of overhead). You can increase this now to 32k (again, minus the same overhead) by changing BLKSZ to 32k in the config.h header.. I'm successfully doing this in my database (which is pretty high-traffic and pretty large). Good luck! -Mitch - Original Message - From: xu hai <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, May 30, 2000 9:11 PM Subject: [SQL] does the' text' type cann't store more than 20,000char ? > hi .everyine > when i want put a long file into a field with text type . i was warning the turple is > too long and the table cann't open again.does the' text' type cann't store more than about 9,050 char ? > thank you . > xv hai >
Re: [SQL] psql problem
> > Does anyone know why when I am in a particular DB as user postgres and use > > the following statement, why I get this error?" > > > > This is the statement; > > SELECT * FROM some_file where ID = 1; > > > > -- -- > > Error: ERROR: attribute 'id' not found > > Execution time 0.02 sec. That indicates that you have no column named 'id'.. > > But if I use the following statement, everything is returned fine. > > > > SELECT * FROM servlet_file; That's a totally different query which would yeild totally different results.. -Mitch
Re: [SQL] SPEED UP.
Lets see your queries you're running and their plan, I'd bet there are ways to speed them up (that's always been the case with mine!).. fields - Mitch "The only real failure is quitting." - Original Message - From: Alessandro Rossi <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, June 02, 2000 2:49 AM Subject: [SQL] SPEED UP. > > > I would like to know is there is a specific way to speed up my query to > postgres. > > I have a DB-MACHINE that I use just and only for postgres witch runs > linux RedHad 6.2 with 1Gb of RAM on ULTRA SCSI discs. > > I run vacuum every hour, but there is something else I can do (compile > postgres in a special way ) to get the best from postgres ?? > > > Thanks in advance > > Alex > > >
[SQL] Orderby two different columns
I ran into a problem today that I hope someone can help me with... I have a database (and application) that is used to track 'applicants'.. These applicants have two timestamp fields associated with their records and both have relevance as to how long the applicant has been available.. The resubmitted field s of type timestamp and has a default value of 'Sat Oct 02 00:00:00 1999 EDT' I need to order search results by the two dates. Here is the problem.. They want whichever date is the most recent to appear on top.. If I do 'order by resubmitted desc,created desc' I get something like this : Applicant Re-submitted Created A 06/05/2000 12/31/1999 B 06/05/2000 12/31/1999 C 05/17/2000 02/09/2000 D 05/17/2000 01/21/2000 E 05/11/2000 01/27/2000 F 05/11/2000 01/21/2000 G 05/01/2000 12/31/1999 H 04/28/2000 01/28/2000 I 04/28/2000 01/12/2000 J 05/23//2000 Ok, see applicant J? I need him to be above C.. Basically what I need to do is order by a combination of date created/resubmitted -- the way I'm doing it now is going to list al the resubmitted's in order, then all the created's in order.. Perhaps I'm just missing something simple, I sure hope so.. Hopefully I've explained it well enough. Thanks for any suggestions!!! -Mitch
[SQL] Speaking of fulltextindex...
I just noticed this in some testing.. When I use my PHP application to insert text into the field that's used in the full text index it takes 9 full seconds, when investigating resource usage using 'top' I see this : Development from a completely idle start up : PID USERNAME PRI NICE SIZERES STATE C TIME WCPUCPU COMMAND 682 postgres 2 0 124M 2328K select 0 0:00 0.00% 0.00% postgres Production server -- this one is being used : PID USERNAME PRI NICE SIZERES STATE C TIME WCPUCPU COMMAND 96825 postgres 2 0 38380K 35464K sbwait 0:04 2.10% 2.10% postgres The backend is started exactly the same way with the same version (7.0.2) on these two servers with the options -B 4096 -o '-S 16384' -- can anyone think of a reason why would one initially grow to 124 megs? I'm waiting to see about that before I continue investigating the sudden exponential increase in my INSERT speed - hopefully it's related (because I sure see why the transaction suddenly take ten times longer to complete than it did!).. Thanks!! -Mitch
Re: [SQL] Need to improve performance
> vacuum; > vacuum analyze; > select f1.id from app_fti f1, app_fti f2 where f1.string~*'visual' and > f2.string~*'basic' and f1.id=f2.id; Use ~*'^basic' It will use the indexes I believe. Also, enable likeplanning (look in contrib/likeplanning) -- it will speed things up too.. If that doesn't help then use EXPLAIN to get your query plan and post it, I'll try to help further... I'm doing this exact thing, so have some experience on tweaking it (Thanks again Tom!) :-) I'd bet what's happening is you're doing a seq scan, not something you want to do on that big of a table. (I know that's what's happening with using ~*'whatever' ) Make good use of the stop words in fti.c too (be sure to order them, it's a binary search). Hope that helps.. -Mitch
[SQL] More full text index..
I hadn't concentrated on the INSERT/UPDATE/DELETE speed of this until today and I find that it's amazingly slow. Of course the time it takes is relative to the size of the text but still, almost a minute to delete one record on a Dual celeron 600 with 256 Megs of RAM and an ATA/66 7200 RPM 30 GIG hard drive... INSERTs seem to be quite a bit faster (which puzzles me) but they're still 10-20 seconds for a single record... UPDATEs seems very fast (a few seconds). I do have a lot of stop works in fti.c, however when I imported the 10,000 text files into the data base it was super fast (before I created indexes) so I'm assuming that the indexes are slowing down the INSERTs UPDATEs and DELETEs, which is expected I think? The database is VACUUMed on a regular basis (and VACUUM ANALYZEed as well). I'd rather have the fast search than the fast data entry, I just want to be absolutely sure that I can't do anything to speed things along.. If I run PGOPTIONS="-d2 -s" psql databasename I get this in the logs on an INSERT -- it doesn't appear to give any stats on the queries that the function called by the fti trigger is doing.. --Here is my insert query (20k of text) -- query: INSERT INTO resumes_fti (string, id) VALUES ($1, $2) ! system usage stats: ! 0.644167 elapsed 0.380151 user 0.126785 system sec ! [0.387579 user 0.149069 sys total] ! 9/2 [13/2] filesystem blocks in/out ! 0/2228 [0/2459] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [3/3] messages rcvd/sent ! 9/4 [16/7] voluntary/involuntary context switches ! postgres usage stats: ! Shared blocks: 20 read, 0 written, buffer hit rate = 99.77% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written CommitTransactionCommand proc_exit(0) Like I said, I just need to know if this is expected or if there might be something (anything) I can do to speed it up.. It's going to be running on a damn fast machine so I'm sure that these times are going to get smaller, if not from just brute force. Thanks guys! -Mitch
Re: [SQL] More full text index..
EXPLAIN on a delete isn't very interesting.. databasename=# explain delete from applicants_resumes where app_id=62908; NOTICE: QUERY PLAN: Index Scan using app_resume_app_id_index on applicants_resumes (cost=0.00..3.70 rows=1 width=6) EXPLAIN I'm not thinking that is what's taking so long though, I think it's the fti trigger. There is another table resumes_fti that has individual words (over 20 million rows) on delete in the applicants_resumes table it searches through and deletes out of that table as well, evidently that's where it's taking forever.. In fit.c I can see the delete query generated, it's as straight forward as they come (DELETE from resumes_fti WHERE ID=) Check this out.. databasename=# explain delete from resumes_fti where id=86370016; NOTICE: QUERY PLAN: Seq Scan on resumes_fti (cost=0.00..394577.18 rows=1956 width=6) EXPLAIN Ouch :-) Now this : query: delete from resumes_fti where id=86370016; ProcessQuery ! system usage stats: ! 94.297058 elapsed 66.381692 user 24.776035 system sec ! [66.399740 user 24.785696 sys total] ! 10926/8 [10926/8] filesystem blocks in/out ! 0/30789 [0/31005] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/0 [3/3] messages rcvd/sent ! 186/1493 [189/1496] voluntary/involuntary context switches ! postgres usage stats: ! Shared blocks: 45945 read, 32 written, buffer hit rate = 3.24% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written CommitTransactionCommand proc_exit(0) shmem_exit(0) exit(0) Most of that is greek to me -- speaking of which, is there any place where these stats are explained a bit? Anyway, do you see anything that could be correctable? Thanks!! -Mitch - Original Message - From: Bruce Momjian <[EMAIL PROTECTED]> To: Mitch Vincent <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Saturday, June 24, 2000 2:33 PM Subject: Re: [SQL] More full text index.. > I would check with EXPLAIN to see when indexes are being used. > > > [ Charset ISO-8859-1 unsupported, converting... ] > > I hadn't concentrated on the INSERT/UPDATE/DELETE speed of this until today > > and I find that it's amazingly slow. Of course the time it takes is relative > > to the size of the text but still, almost a minute to delete one record on a > > Dual celeron 600 with 256 Megs of RAM and an ATA/66 7200 RPM 30 GIG hard > > drive... INSERTs seem to be quite a bit faster (which puzzles me) but > > they're still 10-20 seconds for a single record... UPDATEs seems very fast > > (a few seconds). > > > > I do have a lot of stop works in fti.c, however when I imported the 10,000 > > text files into the data base it was super fast (before I created indexes) > > so I'm assuming that the indexes are slowing down the INSERTs UPDATEs and > > DELETEs, which is expected I think? The database is VACUUMed on a regular > > basis (and VACUUM ANALYZEed as well). > > > > I'd rather have the fast search than the fast data entry, I just want to be > > absolutely sure that I can't do anything to speed things along.. > > > > If I run PGOPTIONS="-d2 -s" psql databasename > > > > I get this in the logs on an INSERT -- it doesn't appear to give any stats > > on the queries that the function called by the fti trigger is doing.. > > > > > > --Here is my insert query (20k of text) -- > > query: INSERT INTO resumes_fti (string, id) VALUES ($1, $2) > > ! system usage stats: > > ! 0.644167 elapsed 0.380151 user 0.126785 system sec > > ! [0.387579 user 0.149069 sys total] > > ! 9/2 [13/2] filesystem blocks in/out > > ! 0/2228 [0/2459] page faults/reclaims, 0 [0] swaps > > ! 0 [0] signals rcvd, 0/0 [3/3] messages rcvd/sent > > ! 9/4 [16/7] voluntary/involuntary context switches > > ! postgres usage stats: > > ! Shared blocks: 20 read, 0 written, buffer hit rate > > = 99.77% > > ! Local blocks: 0 read, 0 written, buffer hit rate > > = 0.00% > > ! Direct blocks: 0 read, 0 written > > CommitTransactionCommand > > proc_exit(0) > > > > Like I said, I just need to know if this is expected or if there might be > > something (anything) I can do to speed it up.. It's going to be running on a > > damn fast machine so I'm sure that these times are going to get smaller, if > > not from just brute force. > > > > Thanks guys! > > > > -Mitch > > > > > > > > > -- > Bruce Momjian| http://www.op.net/~candle > [EMAIL PROTECTED] | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 >
Re: [SQL] case insensitive search
SELECT whatever FROM wherever WHERE lower(yourfield) = 'this'; You can do it with a case inseneitive regex search but they can't use indexes and can become very slow on large tables.. SELECT whatever FROM wherever WHERE yourfield ~* 'this'; lower() does leak a bit of memory from what I've heard on the list but I'm sure someone is working on it.. -Mitch - Original Message - From: Joern Muehlencord <[EMAIL PROTECTED]> To: gpsql-sql <[EMAIL PROTECTED]> Sent: Monday, June 26, 2000 2:14 PM Subject: [SQL] case insensitive search > Hello together, > > how can I handle case insensitive search in a table? > > > > -- > Linux is like wigwam - no windows, no gates, apache inside. > In diesem Sinne > Joern > > >
[SQL] Timestamp indexes
A while back I as told (by Tom Lane I *think*) that timestamp (previously
datetime) fields couldn't be indexed as such and that I should index them
using this method :
CREATE INDEX "applicants_resubmitted" on "applicants" using btree ( date
("resubmitted") "date_ops" );
Since almost all the queries that search that field search it casting the
field to date, I thought that would be OK.. It was for a while (in the 6.5.X
days) but it seems that 7.0.2 is treating this different. I can't get an
index scan on that field no matter what I do.
Any suggestions?
Thanks!
-Mitch
Re: [SQL] Timestamp indexes
select * from applicants as a where (a.created::date > '05-01-2000' or a.resubmitted::date > '05-01-2000') order by (case when a.resubmitted > a.created then a.resubmitted else a.created end) desc limit 10 offset 0 There is one of the queries.. I just remembered that the order by was added since last time I checked it's PLAN (in the 6.5.X days) -- could that be the problem? 8784 records in the applicant database. created and resubmitted are both timestamps. NOTICE: QUERY PLAN: Sort (cost=2011.65..2011.65 rows=4880 width=611) -> Seq Scan on applicants a (cost=0.00..1712.68 rows=4880 width=611) ProcessQuery ! system usage stats: ! 7.489270 elapsed 5.609119 user 1.730936 system sec ! [5.618921 user 1.750540 sys total] ! 1/546 [1/546] filesystem blocks in/out ! 0/9287 [0/9496] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/3 [3/6] messages rcvd/sent ! 7/102 [10/105] voluntary/involuntary context switches ! postgres usage stats: ! Shared blocks: 0 read, 0 written, buffer hit rate = 100.00% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written CommitTransactionCommand Thanks Tom! -Mitch
Re: [SQL] Timestamp indexes
With enable_seqscan off (Same query) Sort (cost=9282.89..9282.89 rows=4880 width=611) -> Index Scan using applicants_created, applicants_resubmitted on applicants a (cost=0.00..8983.92 rows=4880 width=611) ...and.. ! system usage stats: ! 7.541906 elapsed 5.368217 user 2.062897 system sec ! [5.391668 user 2.070713 sys total] ! 1/543 [2/543] filesystem blocks in/out ! 0/9372 [0/9585] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/3 [4/7] messages rcvd/sent ! 7/101 [12/107] voluntary/involuntary context switches ! postgres usage stats: ! Shared blocks: 0 read, 0 written, buffer hit rate = 100.00% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written CommitTransactionCommand Looks like that index scan is very unattractive... I'll look for some other ways to speed up the query a bit.. Thanks! -Mitch - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Mitch Vincent" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, July 21, 2000 1:26 PM Subject: Re: [SQL] Timestamp indexes > "Mitch Vincent" <[EMAIL PROTECTED]> writes: > > select * from applicants as a where (a.created::date > '05-01-2000' or > > a.resubmitted::date > '05-01-2000') order by (case when a.resubmitted > > > a.created then a.resubmitted else a.created end) desc limit 10 offset 0 > > > There is one of the queries.. I just remembered that the order by was added > > since last time I checked it's PLAN (in the 6.5.X days) -- could that be the > > problem? > > Probably. With the ORDER BY in there, the LIMIT no longer applies > directly to the scan (since a separate sort step is going to be > necessary). Now it's looking at a lot more data to be fetched by > the scan, not just 10 records, so the indexscan becomes less attractive. > > Might be interesting to compare the estimated and actual runtimes > between this query and what you get with "set enable_seqscan to off;" > > regards, tom lane >
Re: [SQL] query optimazation & starting postmaster with -B option
As I've found through countless trial and error and many emails to this list, performance is mostly in how you structure queries and how you use the backend (indexes, proper VACUUMing etc etc).. Increasing the size passed as -S and -B options will help -- there is probably much more that can be done if you can get specific with us about your tables and what queries you're having trouble with.. Good luck!! -Mitch - Original Message - From: "sathya priya" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, July 22, 2000 4:34 AM Subject: [SQL] query optimazation & starting postmaster with -B option > hai, > > Q 1 : > > > Posgresql is running on Linux machine with 512MB ram. > My question is When i start the postmaster with -B > 3000 then there is no problem. If the start the > postmaster with more than the 3000 then errors msg is > popped out. How do I increase this buffer option > morethan 3000 and what are things to be considered > before doing this > > > > Q 2: postmaster with -N option. > doc says 32 connection is default . > I need more than that 32 connection. Before increase > this connection to 100 what things (like > memory,processor speed .. etc ) we have to take > consideration. > > Q3 : If i increase the -B more than 3000 then will > postgresql execute the query very fast > > > thanks advance. > > kind regds. > p. ashok kumar > > > __ > Do You Yahoo!? > Get Yahoo! Mail - Free email you can access from anywhere! > http://mail.yahoo.com/ >
[SQL] order by x DESC, y ASC indexing problem
Hello, I saw a posting regarding this issue in august, with no solution... How to have the planner use an index in the case of a query like : SELECT * FROM TABLE1 ORDER BY X DESC, Y ASC; (X is a date and Y a varchar) What would that index be? Is there a function I can use, to invert x (the date), so that I can make a query / index set like : CREATE INDEX INDEX1 ON TABLE 1 (INVERT(X), Y ASC); SELECT * FROM TABLE1 ORDER BY INVERT(X) ASC, Y ASC; Wouldn't it be great to have a mySQL, SAPDB-like syntax of the sort : CREATE INDEX INDEX1 ON TABLE 1 (X DESC, Y ASC); Thanks, vincent ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
