Thread: Index usage ?
Hi, I'm making a database with table of over 50 000 000 i created index on some columns but they are used only in range search (e.g. WHERE column<100 AND column>10) and point rearch (e.g. WHERE column=100). When I try to execute some queries using only <,>,<= or >= index is not used. So is there any way to force ysage of the index with this conditions. I'm using BTREE as noted in Pg users guide. please help! regards, Rumen __________________________________________________ Do You Yahoo!? Yahoo! Shopping - Thousands of Stores. Millions of Products. http://shopping.yahoo.com/
I am having similar problems at the moment, the query planner seems to be a little out when deciding which method to use. I disabled the use of seq scans using SET ENABLE_SEQSCAN = NO; and the query that used to take 3.03 minutes now takes 5-7 secs. (v7.0 approx 3.5 million rows) However this has raised a few questions of my own ie. can this setting be forced permanently (rather than for each session) and on individual dbs? It is almost certainly the case that this particular table/db is a special case and I do not want to force index scans on the other dbs. Cheers Graham -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of R D Sent: 12 December 2000 14:20 To: pgsql-admin@postgresql.org Subject: [ADMIN] Index usage ? Hi, I'm making a database with table of over 50 000 000 i created index on some columns but they are used only in range search (e.g. WHERE column<100 AND column>10) and point rearch (e.g. WHERE column=100). When I try to execute some queries using only <,>,<= or >= index is not used. So is there any way to force ysage of the index with this conditions. I'm using BTREE as noted in Pg users guide. please help! regards, Rumen __________________________________________________ Do You Yahoo!? Yahoo! Shopping - Thousands of Stores. Millions of Products. http://shopping.yahoo.com/
Hi all,
We have a pretty significant problem with the current 4k (or is it 8k)
limit in length of a single SQL statement. I remember reading somewhere that
this limit has been (will be) completely removed in 7.1 but I'd like to
verify this. Also if anyone has a reasonable guess to 7.1's release date I'd
be very interested.
Oh yeah, also, is the limit changeable at compile time? If so, if we set it
to something like 200k, would that work?
Thanks,
--Rainer
If I understand your question, you can change this limit, but not to 200k. By default, the data limit in a field record is 8k in the PostgreSQL 7.0x series. You can make it 16k or 32k. Go into the src/include subdir of whatever directory you extracted the tarball into. Before compiling, edit the file called "config.h.in". Change the line from #define BLCKSZ 8192 to #define BLCKSZ 32768 (for 32k limit). -Serge > Oh yeah, also, is the limit changeable at compile time? If so, if we set it > to something like 200k, would that work? > > Thanks, > > --Rainer > >
I was unclear on this, although I think the answer below is correct for my real question. Let me ask this question again... What is the current size limit for a single row (say for type TEXT or VARCHAR)? When is this expected to go away (7.1?)? Is the info below accurate? Am I limited to 32k (if I rebuild)? Thanks, --Rainer -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Serge Canizares Sent: Wednesday, December 20, 2000 12:18 AM To: Rainer Mager Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] SQL statement limit If I understand your question, you can change this limit, but not to 200k. By default, the data limit in a field record is 8k in the PostgreSQL 7.0x series. You can make it 16k or 32k. Go into the src/include subdir of whatever directory you extracted the tarball into. Before compiling, edit the file called "config.h.in". Change the line from #define BLCKSZ 8192 to #define BLCKSZ 32768 (for 32k limit). -Serge