Thread: Index usage ?

Index usage ?

From
R D
Date:
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/

RE: Index usage ?

From
"Graham Vickrage"
Date:
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/


SQL statement limit

From
"Rainer Mager"
Date:
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


Re: SQL statement limit

From
"Serge Canizares"
Date:
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
>
>


RE: SQL statement limit

From
"Rainer Mager"
Date:
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