FAQ

PostgreSQL > pgsql-performance > August 2004

Search Discussions

73 discussions - 491 posts

  • Coming from the MSSQL world, I'm used to the first step in optimization to be, choose your clustered index and choose it well. I see that PG has a one-shot CLUSTER command, but doesn't support ...
    Mischa SandbergMischa Sandberg
    Aug 25, 2004 at 5:31 am
    Sep 8, 2004 at 4:06 pm
  • I run a Perl/CGI driven website that makes extensive use of PostgreSQL (7.4.3) for everything from user information to formatting and display of specific sections of the site. The server itself, is a ...
    Martin FosterMartin Foster
    Aug 3, 2004 at 6:19 pm
    Aug 10, 2004 at 8:05 pm
  • Hi All, We're currently running Postgres 7.4.1 on FreeBSD 5.2, a Dual Xeon 2.4, 2GB ECC, 3Ware Serial ATA RAID 5 w/ 4 disks (SLOW!!). Our database is about 20GB on disk, we have some quite large ...
    Jason CoeneJason Coene
    Aug 10, 2004 at 7:17 pm
    Aug 12, 2004 at 7:27 pm
  • The query: select count(*) from billing where timestamp now()-60 should obviously use the index CREATE INDEX billing_timestamp_idx ON billing USING btree ("timestamp" timestamp_ops); on a table with ...
    Jack KerkhofJack Kerkhof
    Aug 27, 2004 at 5:12 pm
    Sep 1, 2004 at 12:50 pm
  • We have a web based application with data that is updated daily. The biggest bottleneck occurs when we try to update one of the tables. This table contains 58,000 rows and 62 columns, and EVERY ...
    Ron St-PierreRon St-Pierre
    Aug 31, 2004 at 6:09 pm
    Sep 7, 2004 at 5:05 pm
  • Dear all: Im having a weird problem here. I have a table w/ ~180.000 rows and I want to select those where c 0 or d 0 (there only a few of those on the table) I indexed columns c and d (separately) ...
    Martin SarsaleMartin Sarsale
    Aug 30, 2004 at 5:46 pm
    Sep 1, 2004 at 5:53 pm
  • Just curious if folks have ever used this for a postgresql server and if they used it with OSX/BSD/Linux. Even if you haven't used it, if you know of something comparable I'd be interested. TIA ...
    Robert TreatRobert Treat
    Aug 25, 2004 at 9:09 pm
    Aug 27, 2004 at 8:34 pm
  • Can anyone give a good reference site/book for getting the most out of your postgres server. All I can find is contradicting theories on how to work out your settings. This is what I followed to ...
    Paul SerbyPaul Serby
    Aug 4, 2004 at 12:45 pm
    Aug 9, 2004 at 2:47 pm
  • Hi, I have some problem of performance on a PG database, and I don't know how to improve. I Have two questions : one about the storage of data, one about tuning queries. If possible ! My job is to ...
    Valerie Schneider DSI/DEVValerie Schneider DSI/DEV
    Aug 4, 2004 at 12:43 pm
    Aug 6, 2004 at 4:11 pm
  • Hi, is there anything I can doo to speed up inserts? One of my tables gets about 100 new rows every five minutes. And somehow the inserts tend to take more and more time. Any suggestions welcome. TIA ...
    Ulrich WisserUlrich Wisser
    Aug 13, 2004 at 7:47 am
    Aug 14, 2004 at 2:41 pm
  • hi, I have the following structure in my base 7.4.2 CREATE TABLE "public"."article" ( "art_id" INTEGER NOT NULL, "rub_id" INTEGER DEFAULT '0' NOT NULL, "art_titre" VARCHAR(100) DEFAULT '' NOT NULL, ...
    Jean-Max ReymondJean-Max Reymond
    Aug 31, 2004 at 6:59 pm
    Aug 31, 2004 at 8:41 pm
  • Hi all, I'm tring to optimize the following query: http://rafb.net/paste/results/YdO9vM69.html as you can see from the explain after defining the index the performance is worst. If I raise the ...
    Gaetano MendolaGaetano Mendola
    Aug 19, 2004 at 5:43 pm
    Aug 22, 2004 at 10:08 am
  • I'm thinking of upgrading an existing dedicated server and colocating my own server. The server is used for prototype systems running Postgresql, php and apache. The largest database is presently ...
    Rory Campbell-LangeRory Campbell-Lange
    Aug 9, 2004 at 4:57 pm
    Aug 19, 2004 at 4:20 pm
  • You are getting 300-400 inserts/sec with fsync on? If you don't mind me asking, what's your hardware? (also, have you checked fsync on #s with the new bgwriter in 7.5?) Merlin
    Merlin MoncureMerlin Moncure
    Aug 10, 2004 at 3:26 pm
    Aug 13, 2004 at 7:58 pm
  • Hi list, I have a database with 1M "people" in it. Each person has about 20 attributes, such as height, weight, eye color, etc. I need to be able to search for people based on these attributes. A ...
    Daniel CeregattiDaniel Ceregatti
    Aug 19, 2004 at 6:03 pm
    Aug 26, 2004 at 4:49 am
  • Hi I have 2 tables like this: CREATE TABLE query ( query_id int not null, dat varchar(64) null , sub_acc_id int null , query_ip varchar(64) null , osd_user_type varchar(64) null ) ; CREATE TABLE ...
    Marius AndreianaMarius Andreiana
    Aug 3, 2004 at 8:40 am
    Aug 4, 2004 at 1:31 am
  • Hi, I'm a little beginner with Tsearch2 .... I have simples tables like this : # \d article Table "public.article" Column | Type | Modifiers ...
    Hervé PiedvacheHervé Piedvache
    Aug 25, 2004 at 10:48 pm
    Sep 13, 2004 at 3:04 am
  • 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 ...
    My thi hoMy thi ho
    Aug 18, 2004 at 12:44 am
    Aug 27, 2004 at 6:27 am
  • HI all, I'm trying to implement a highly-scalable, high-performance, real-time database replication system to back-up my Postgres database as data gets written. So far, Mammoth Replicator is looking ...
    Chris ChestonChris Cheston
    Aug 14, 2004 at 1:27 am
    Aug 16, 2004 at 3:12 pm
  • Hi all, I've attached all the query in query.sql I'm using postgres 7.3.4 on Linux version 2.4.26-custom ( /proc/sys/vm/overcommit_memory = 0 this time ) free : total used free shared buffers cached ...
    StefStef
    Aug 24, 2004 at 8:32 am
    Aug 25, 2004 at 2:31 pm
  • Hi verybody! I can't 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. ...
    Igor ArtimenkoIgor Artimenko
    Aug 17, 2004 at 2:23 pm
    Aug 20, 2004 at 1:45 pm
  • Hi, We are having a performance problem with our database. The problem exists when we include a constraint in GCTBALLOT. The constraint is as follows: alter table GCTBALLOT add constraint ...
    Arash ZaryounArash Zaryoun
    Aug 13, 2004 at 2:44 pm
    Aug 14, 2004 at 10:57 am
  • Greetings. I have a question regarding performance of certain datatypes: I have a field where I will store my clients phone numbers. I know that this field will never exceed 15 characters, and I will ...
    Er Galvão AbbottEr Galvão Abbott
    Aug 11, 2004 at 5:41 am
    Aug 11, 2004 at 7:20 pm
  • Hi, I have a question on bulk checking, inserting into a table and how best to use an index for performance. The data I have to work with is a monthly CD Rom csv data dump of 300,000 property owners ...
    Rudi StarcevicRudi Starcevic
    Aug 10, 2004 at 11:06 pm
    Aug 11, 2004 at 4:13 am
  • I have 2 servers both with the exact same data, the same O.S., the same version of Postgres (7.4.5) and the exact same db schema's (one production server, one development server). One server is using ...
    David PriceDavid Price
    Aug 25, 2004 at 3:06 pm
    Aug 26, 2004 at 11:04 am
  • 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; ...
    olivier HAROolivier HARO
    Aug 17, 2004 at 1:30 pm
    Aug 17, 2004 at 4:59 pm
  • This topic really belongs on the performance list. I have copied that list and set followups to go there and copy you. This sounds like you aren't doing proper maintainance. You need to be vacuuming ...
    Bruno Wolff IIIBruno Wolff III
    Aug 11, 2004 at 4:18 pm
    Aug 12, 2004 at 10:52 pm
  • Hi all, We have table q_20040805 and a delete trigger on it. The delete trigger is: update table q_summary set count=count-1... When we delete from q_20040805, we also insert into related info ...
    Litao WuLitao Wu
    Aug 10, 2004 at 12:06 am
    Aug 10, 2004 at 4:53 pm
  • Hi, i would like to answer if there is any way in postgres to find the page miss hits caused during a query execution. Is there something like explain analyze with the page miss hits???
    Ioannis TheoharisIoannis Theoharis
    Aug 2, 2004 at 8:11 am
    Aug 2, 2004 at 10:18 pm
  • Hi, I have just installed 8.0.0beta1 and I noticed that some query are slower than 7.4.2 queries. After: pg_dump my_database mydb.sql (from 7.4.2) psql my_new_database <mydb.sql (to 8.0.0 with COPY ...
    Stefano BonninStefano Bonnin
    Aug 27, 2004 at 6:54 am
    Sep 1, 2004 at 9:15 am
  • RT uses a query like: SELECT distinct main.oid,main.* FROM Tickets main WHERE (main.EffectiveId = main.id) AND (main.Status != 'deleted') AND ( (main.Type = 'ticket') OR (main.Type = 'subticket') ) ...
    Dave CramerDave Cramer
    Aug 19, 2004 at 1:15 pm
    Aug 19, 2004 at 2:21 pm
  • 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 * ...
    Frank van VugtFrank van Vugt
    Aug 17, 2004 at 9:30 pm
    Aug 18, 2004 at 11:03 pm
  • Using this SQL: EXPLAIN ANALYZE SELECT DISTINCT sessionid, '2004-33' AS "yearweek", nd.niveau INTO TEMP distinct_session FROM httplog h ,niveaudir nd WHERE hitDateTime ('now'::timestamp with time ...
    Ole TangeOle Tange
    Aug 15, 2004 at 5:48 pm
    Aug 15, 2004 at 7:40 pm
  • Hi. Please be a bit patient.. I'm quite new to PostgreSQL. I'd like some advise on storing binary data in the database. Currently I have about 300.000 320.000 Bytes "Bytea" records in the database. ...
    Jesper KroghJesper Krogh
    Aug 11, 2004 at 2:29 pm
    Aug 11, 2004 at 11:46 pm
  • Having trouble with one table (see time to count records below!). Fairly new to postgres so any help much appreciated. It only contains 9,106 records - as you can see from: select count(id) from ...
    Paul LangardPaul Langard
    Aug 9, 2004 at 4:54 pm
    Aug 10, 2004 at 9:44 pm
  • Hi all, My system is a PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 20020903 (Red Hat Linux 8.0 3.2-7). It has a Pentium III-733 Mhz with 512 MB ram. It is connected to my ...
    Joost KraaijeveldJoost Kraaijeveld
    Aug 2, 2004 at 12:21 pm
    Aug 2, 2004 at 4:10 pm
  • We have a "companies" and a "contacts" table with about 3000 records each. We run the following SQL-Command which runs about 2 MINUTES !: SELECT count(*) FROM contacts LEFT JOIN companies ON ...
    MailingMailing
    Aug 2, 2004 at 12:08 pm
    Aug 2, 2004 at 4:07 pm
  • Hi everybody! Here is my queries: 1. explain SELECT * FROM messageinfo WHERE user_id = CAST( 20000 AS BIGINT ) and msgstatus = CAST( 0 AS smallint ); 2. explain SELECT * FROM messageinfo WHERE ...
    Artimenko IgorArtimenko Igor
    Aug 27, 2004 at 7:29 pm
    Aug 28, 2004 at 8:51 am
  • Hi, I got this error message: LOG: could not create IPv6 socket: Address family not supported by protocol LOG: could not bind socket for statistics collector: Cannot assign requested address LOG: ...
    My thi hoMy thi ho
    Aug 23, 2004 at 2:33 pm
    Aug 23, 2004 at 10:30 pm
  • HI All, I have a big performance issue concerning a PostgreSQL database. I have the following server configuration: Pentium 4 2.4 GHz 1 GB RAM 36 GB SCSI And the following tables: TABLES ...
    Danilo MotaDanilo Mota
    Aug 20, 2004 at 4:20 pm
    Aug 20, 2004 at 4:49 pm
  • Hello, This is not strictly PostgreSQL performance hint, but may be helpful to someone with problems like mine. As I earlier posted, I was experiencing very high load average on one of my Linux ...
    ElevenEleven
    Aug 18, 2004 at 8:18 am
    Aug 18, 2004 at 1:10 pm
  • Hi all, I'm running postgres 7.3.4 on a quad Xeon 2.8 GHz with Mem: 1057824768 309108736 748716032 0 12242944 256413696 Swap: 518053888 8630272 509423616 on Linux version 2.4.26-custom Data directory ...
    StefStef
    Aug 16, 2004 at 3:22 pm
    Aug 17, 2004 at 6:24 am
  • Hi all! I'm new here, so hello to everybody! I'm in a deep truble using postgesSQL 7.2.0 on a low-end pc with SUSE 8. I'm using some databases from that pc through odbc (7.3.200). Until now i had no ...
    Kroh IstvánKroh István
    Aug 31, 2004 at 5:26 pm
    Aug 31, 2004 at 7:01 pm
  • Hi all, do you know any clean workaround at ill-planned queries inside a stored procedure? Let me explain with an example: empdb=# select count(*) from user_logs; count --------- 5223837 (1 row) ...
    Gaetano MendolaGaetano Mendola
    Aug 28, 2004 at 11:14 am
    Aug 28, 2004 at 4:35 pm
  • I am using a simple PostgreSQL 7.3 database in a soft-realtime application. I have a problem where an update on a record within a (fully indexed) table containing less than ten records needs to occur ...
    Neil CooperNeil Cooper
    Aug 26, 2004 at 6:03 pm
    Aug 26, 2004 at 6:20 pm
  • Hi, I noticed an interesting phenomenon when loding (COPY) some tables from a file. For some reason, one load was taking longer than I assumed it would. As it turns out, one of the columns was an ...
    Aaron BirklandAaron Birkland
    Aug 18, 2004 at 7:39 pm
    Aug 18, 2004 at 9:47 pm
  • Hi everybody! I can’t 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. ...
    Artimenko IgorArtimenko Igor
    Aug 17, 2004 at 3:35 pm
    Aug 17, 2004 at 6:32 pm
  • I thought this could generate some interesting discussion. Essentially, there are three queries below, two using sub-queries to change the way the randomized information (works first by author and ...
    Martin FosterMartin Foster
    Aug 15, 2004 at 3:11 am
    Aug 15, 2004 at 4:01 pm
  • select count(*) FROM items_2004_07_29 as items WHERE true AND index @@ to_tsquery('default', '( audiovox)') ; count ------- 4 (1 row) aers=# reindex index idx_title_2004_07_29; REINDEX aers=# select ...
    BorajettaBorajetta
    Aug 13, 2004 at 9:40 pm
    Aug 13, 2004 at 10:25 pm
  • Does the order of columns of varying size have any effect on SELECT/INSERT/UPDATE/and/or/DELETE performance? Take the example where an integer primary key is listed first in the table and ...
    Bill MontgomeryBill Montgomery
    Aug 10, 2004 at 1:39 pm
    Aug 10, 2004 at 6:49 pm
Aug 2004
12
Group Navigation
period‹ prev | Aug 2004 | next ›
Group Overview
grouppgsql-performance @
categoriespostgresql
discussions73
posts491
users133
websitepostgresql.org
irc#postgresql

133 users for August 2004

Tom Lane: 39 posts Gaetano Mendola: 36 posts Josh Berkus: 27 posts Merlin Moncure: 22 posts Martin Foster: 14 posts Pierre-Frédéric Caillaud: 14 posts Bruce Momjian: 12 posts Greg Stark: 12 posts Scott Marlowe: 12 posts Rod Taylor: 11 posts Christopher Kings-Lynne: 10 posts Jan Wieck: 8 posts Richard Huxton: 8 posts Bruno Wolff III: 7 posts Jason Coene: 7 posts Steinar H. Gunderson: 7 posts Jean-Max Reymond: 6 posts Leeuw van der, Tim: 6 posts Gnari: 5 posts Mark Kirkwood: 5 posts
show more
Archives