On 12.05.2005, at 16:15, Tom Lane wrote:
David Teran <[EMAIL PROTECTED]> writes:
Any hint how
to force postgres to use the index even with more OR parts?
More up-to-date statistics would evidently help; the thing is
estimating
hundreds of rows returned and actually finding none.
I alway
Hi,
postgres 8.0.1, mac os x 10.3.9
i have a select with multiple OR's combined with one AND:
explain analyze SELECT t0.ATTRIBUTE_TYPE FROM ATTRIBUTE_VALUE t0 WHERE
(((t0.ATTRIBUTE_TYPE = 'pb'::varchar(10) OR t0.ATTRIBUTE_TYPE =
'po'::varchar(10) OR t0.ATTRIBUTE_TYPE = 'pn'::varchar(10) OR
t0.AT
Hi,
we have a table with about 6.000.000 rows. There is an index on a
column with the name id which is an integer and serves as primary key.
When we execute select max(id) from theTable; it takes about 10
seconds. Explain analyze returns:
--
Hi,
On 08.03.2004, at 02:29, Christopher Kings-Lynne wrote:
explain analyze select
((t0.int_value-t1.int_value)*(t0.int_value-t1.int_value))
from job_property t0, job_property t1
where t0.id_job_profile = 5
and t1.id_job_profile = 6
and t1.id_job_attribute = t0.id_job_attribute
and t1.int_value <
Hi,
we need to optimize / speed up a simple select:
explain analyze select
((t0.int_value-t1.int_value)*(t0.int_value-t1.int_value))
from job_property t0, job_property t1
where t0.id_job_profile = 5
and t1.id_job_profile = 6
and t1.id_job_attribute = t0.id_job_attribute
and t1.int_value < t0.int
Hi Nick,
Try using:
SELECT id FROM theTable ORDER BY is DESC LIMIT 1;
Using COUNT, MAX, MIN and any aggregate function on the table of that
size will always result in a sequential scan. There is currently no
way around it although there are a few work arounds. See the
following for more infor
Hi,
Is your int_value data type int4? If not then use "... from
job_property
where int_value = '0'"
Indexes are used only if datatypes matches.
tried those variations already. Strange enough, after dropping and
recreating the index everything worked fine.
regards David
---
Hi
we have a table with about 4 million rows. One column has an int value,
there is a btree index on it. We tried to execute the following
statement and it is very slow on a dual G5 2GHZ with 4 GB of RAM.
explain analyze select count(*) from job_property where int_value = 0;
Aggregate (cost=1
Hi,
we are trying to speed up a database which has about 3 GB of data. The
server has 8 GB RAM and we wonder how we can ensure that the whole DB
is read into RAM. We hope that this will speed up some queries.
regards David
---(end of broadcast)--
Hi,
I'm not sure ... I thought I ran it on my P4 here in the office and
saw it
too, albeit not near as frequently ... but, in FreeBSD's case, it is a
"design issue" ... there are two different functions, once that is
kinda
fuzzy (but fast), and the other that is designed to be exact, but at a
pe
Hi Tim,
you are right:
Interesting. I have recollected where we saw this before:
http://archives.postgresql.org/pgsql-hackers/2003-11/msg01528.php
Apparently gettimeofday() has a glitch on some BSD releases. OS X is
a BSD derivative and it's not so surprising if it has it too.
May I suggest th
HI Tom.
I got a little distracted by the bizarre actual-time values shown for
some of the query steps:
-> Merge Join (cost=2451266.53..2655338.83 rows=13604393
width=8)
(actual time=82899.466..-2371037.726 rows=2091599 loops=1)
-> Sort (cost=2451169.10..2483246.47 rows=12830947
Hi,
On 30.01.2004, at 19:10, Stephan Szabo wrote:
On Fri, 30 Jan 2004, David Teran wrote:
select
sum(job_property_difference(t0.int_value, t1.int_value)) as rank
from
job_property t0,
job_property t1
where
t0.id_job_profile = 911
and t0.id_job_attribute = t1.id_job_attribute
Hi,
its me again. As far as we tested postgresql ist fast, very fast
compared to the other db system we test and are using currently.
We are now testing some test databases on Postgres. We use one
function which simply calculates a difference between two values and
checks if on value is 0,
... wow:
executing a batch file with about 4250 selects, including lots of joins
other things PostgreSQL 7.4 is about 2 times faster than FrontBase
3.6.27. OK, we will start to make larger tests but this is quite
interesting already: we did not optimize a lot, just invoked VACUUM
ANALYZE and t
Hi Tom,
first of all thanks for your help! I really appreciate your fast
response and if you ever have a question about WebObjects, just drop me
line ;-)
Seq Scan on "KEY_VALUE_META_DATA" t0 (cost=0.00..2671.16 rows=931
width
=1068) (actual time=122.669..172.179 rows=25 loops=1)
Filter:
Hi Tom,
David Teran <[EMAIL PROTECTED]> writes:
What we found out now is that a query with a single 'where' works
fine,
the query planer uses the index but when we have 'two' where clauses
it
does not use the index anymore:
EXPLAIN ANALYZE SELECT columns... FRO
Hi Tom,
It's worth pointing out that this problem is fixed (at long last) in
CVS tip. Ypu probably shouldn't expend large amounts of effort on
working around a problem that will go away in 7.5.
We have now changed the definition to integer, this will work for some
time. We are currently evaluati
Hi,
The performance will likely to be the same. Its just that integer
happens to
be default integer type and hence it does not need an explicit
typecast. ( I
don't remember exactly which integer is default but it is either of
int2,int4
and int8...:-))
The docs say int4 is much faster than int8
Hi Shridhar,
Are you sure you are using correct data types on indexes?
Did not know about this...
e.g. if field1 is an int2 field, then following query would not use an
index.
our fk have the type bigint, when i try one simple select like this:
explain analyze SELECT --columns-- FROM KEY_VALU
Hi,
we are new to Postgres and we are evaluating Postgres 7.4 on MacOS X as
an alternative to FrontBase 3.6.27.
From the available features Postgres is the choice #1.
We have some tests to check the performance and FrontBase is about 10
times faster than Postgres. We already played around with
21 matches
Mail list logo
