Hi,
I am working on a project which explore postgresql to
store multimedia data.
In details, i am trying to work with the buffer
management part of postgres source code. And try to
improve the performance. I had search on the web but
could not find much usefull information.
It would be great if a
Frank,
> It seems in this case the time needed for a single deferred trigger somehow
> depends on the number of dead tuples in the table, because a vacuum of the
> table will 'reset' the query-times. However, even if I wanted to, vacuum is
> not allowed from within a function.
>
> What is happenin
Obviously,
this part of tr_f_def():
**
-- delete the contents
-- delete from f;
IF EXISTS (SELECT 1 FROM f) THEN
DELETE FROM F;
VACUUM F;
END IF;
**
Hi,
I'm seeing the following behaviour with the table and functions given below:
db=# insert into f select * from full_sequence(1, 1000);
INSERT 0 1000
Time: 197,507 ms
db=# insert into f select * from full_sequence(1, 1000);
INSERT 0 1000
Time: 341,880 ms
db=# insert into f select * from full_se
I'm doing a nightly vacuum... so I don't think that's it, although should I be doing a FULL vacuum instead? The size of my data directory is only about 389 MB. I'll take a closer look at file sizes going forward.
echo "VACUUM ANALYZE VERBOSE;" | /Library/PostgreSQL/bin/psql -U postgres officelink
test where id = 5;â Few times I added 100,000 records, applied
cast the 5 to int8 and it will use the index
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes
An index
on cp and effectif would help your first query. An index on naf, cp and effectif would help your second
query.
Something
like this:
CREATE INDEX base_aveugle_cp_key2 ON
base_aveugle USING btree (cp, effectif);
CREATE INDEX base_aveugle_naf_key2 ON base_aveugle USING btree
Artimenko Igor wrote:
id int8 NOT NULL DEFAULT nextval('next_id_seq'::text) INIQUE,
ID column is bigint, but '5' is int, therefore the index does not
match. You need to cast your clause like this:
select id from test where id = 5::int8
Also, issue VACUUM ANALYZE, so Postgres knows about
Thanks for the tip for the index on multiple columns ! (I never do inserts
on this table so insert time doesn't matter)
Mys posgresql version is : PostgreSQL 7.2.1
Here are the results of the EXPLAIN ANALYZE you asked me to execute.
explain analyse select cp from base_aveugle where cp='69740'
Hi everybody!
I cant make use of indexes even I tried the same test by changing different settings
in
postgres.conf like geqo to off/on & geqo related parameters, enable_seqscan off/on &
so on. Result
is the same.
Here is test itself:
Ive created simplest table test and executed the same st
olivier HARO wrote:
This table contains 5 000 000 records
I have a PHP application which often makes queries on this table (especially on the
"cp","naf","effectif" fields)
Querries are like :
select (distint cp) from base_aveugle where cp='201A' and effectif between 1 and
150
select (
olivier HARO wrote:
Hello,
I have a dedicated server for my posgresql database :
P4 2.4 GHZ
HDD IDE 7200 rpm
512 DDR 2700
I have a problem whith one table of my database :
CREATE SEQUENCE "base_aveugle_seq" START 1;
CREATE TABLE "base_aveugle" (
"record_id" integer DEFAULT nextval('"base_a
Hi,
Make
multi-column indexes, using the columns from your most typical queries, putting
the most selective columns first (ie; you don't need to make indexes with
columns in the same order as they are used in the query).
For
instance, an index on cp, effectif could likely benefit both qu
Hello,
I have a dedicated server for my
posgresql database :
P4 2.4 GHZ
HDD IDE 7200 rpm
512 DDR 2700
I have a problem whith one table of
my database :
CREATE SEQUENCE "base_aveugle_seq" START
1;CREATE TABLE "base_aveugle" ( "record_id" integer DEFAULT
nextval('"base_aveugle_seq"'::t
14 matches
Mail list logo
