FAQ

PostgreSQL > pgsql-performance > January 2007

Search Discussions

58 discussions - 451 posts

  • Running PostgreSQL 8.2.1 on Win32. The query planner is choosing a seq scan over index scan even though index scan is faster (as shown by disabling seqscan). Table is recently analyzed and row count ...
    Jeremy HaileJeremy Haile
    Jan 16, 2007 at 9:23 pm
    Jan 19, 2007 at 7:16 pm
  • Greetings! I have rather large table with about 5 millions of rows and a dozen of columns. Let's suppose that columns are named 'a', 'b', 'c' etc. I need to query distinct pairs of ('a';'b') from ...
    Igor LobanovIgor Lobanov
    Jan 30, 2007 at 8:59 am
    Jan 31, 2007 at 3:16 pm
  • Hello all! Running a vac on an 8.2 client connecting to an 8.2 database (following example was for a 14 row static table) - vacuums will sit (for lack of a better word) for anywhere from ten to ...
    KimKim
    Jan 11, 2007 at 5:47 pm
    Jan 17, 2007 at 4:56 pm
  • Hi all, I'm not sure if this question fits in the topic of this list. I'm interested in partitioning and it's the first time I'd use it. There is an issue I don't know how you handle it. Lets say I'm ...
    ArnauArnau
    Jan 5, 2007 at 11:02 am
    Jan 30, 2007 at 10:21 am
  • Hmm... This gets stranger and stranger. When connecting to the database with the psql client in 8.2's bin directory and using commands such as \d the client hangs, or takes an extremely long time. If ...
    Erik JonesErik Jones
    Jan 2, 2007 at 9:59 pm
    Jan 10, 2007 at 8:08 pm
  • Hi, I have a slow response of my PostgreSQL database 7.4 using this query below on a table with 800000 rows: select count(*)from tbl; PostgreSQL return result in 28 sec every time. although MS-SQL ...
    Laurent ManchonLaurent Manchon
    Jan 23, 2007 at 10:35 am
    Jan 24, 2007 at 10:19 am
  • Hi there, we've partioned a table (using 8.2) by day due to the 50TB of data (500k row size, 100G rows) we expect to store it in a year. Our performance on inserts and selects against the master ...
    Colin TaylorColin Taylor
    Jan 7, 2007 at 4:37 am
    Jan 12, 2007 at 7:52 pm
  • Hi, For I can not find too much information about how to use vacuum, I want to ask some general information about the guideline of vacuum planning. 1. How do we know if autovacuum is enough for my ...
    Galy LeeGaly Lee
    Jan 24, 2007 at 5:38 am
    Jan 26, 2007 at 9:08 pm
  • This is a query migrated from postgres. In postgres it runs about 10,000 times *slower* than on informix on somewhat newer hardware. The problem is entirely due to the planner. This PostgreSQL 8.1.4 ...
    Gregory S. WilliamsonGregory S. Williamson
    Jan 9, 2007 at 11:54 am
    Jan 10, 2007 at 2:54 pm
  • I've got a table with a few million rows, consisting of a single text column. The average length is about 17 characters. For the sake of an experiment, I put a trigram index on that table. ...
    Florian WeimerFlorian Weimer
    Jan 15, 2007 at 10:16 am
    Feb 26, 2007 at 1:10 pm
  • Hi, I find various references in the list to this issue of queries being too slow because the planner miscalculates things and decides to go for a sequenctial scan when an index is available and ...
    Carlos MorenoCarlos Moreno
    Jan 27, 2007 at 3:14 am
    Jan 27, 2007 at 9:10 pm
  • I am developing an application that has very predictable database operations: -inserts several thousand rows into 3 tables every 5 minutes. (table contain around 10 million rows each) -truncates and ...
    Jeremy HaileJeremy Haile
    Jan 9, 2007 at 5:30 pm
    Jan 11, 2007 at 3:42 am
  • I am sure that this has been discussed before, but I can't seem to find any recent posts. (I am running PostgreSQL 8.2) I have always ran PostgreSQL on Linux in the past, but the company I am ...
    Jeremy HaileJeremy Haile
    Jan 3, 2007 at 5:27 pm
    Jan 10, 2007 at 8:45 pm
  • Hi! I'm planning to move from mysql to postgresql as I believe the latter performs better when it comes to complex queries. The mysql database that I'm running is about 150 GB in size, with 300 ...
    John ParnefjordJohn Parnefjord
    Jan 26, 2007 at 11:28 am
    Feb 6, 2007 at 10:21 am
  • Check this: query: Delete From ceroriesgo.salarios Where numero_patrono Not In (Select numero_patrono From ceroriesgo.patronos) Seq Scan on salarios (cost=51021.78..298803854359.95 rows=14240077 ...
    Sidar López CruzSidar López Cruz
    Jan 30, 2007 at 9:09 pm
    Feb 1, 2007 at 1:30 am
  • Hello - I have a fairly large table (3 million records), and am fetching 10,000 non-contigous records doing a simple select on an indexed column ie select grades from large_table where teacher_id = X ...
    Mark DobbrowMark Dobbrow
    Jan 13, 2007 at 12:31 am
    Jan 14, 2007 at 7:20 pm
  • We have a table with a timestamp attribute (event_time) and a state flag which usually changes value around the event_time (it goes to 4). Now we have more than two years of events in the database, ...
    Tobias BroxTobias Brox
    Jan 12, 2007 at 8:16 am
    Jan 12, 2007 at 10:56 am
  • I'm building an e-mail service that has two requirements: It should index messages on the fly to have lightening search results, and it should be able to handle large amounts of space. The server is ...
    Charles A. LandemaineCharles A. Landemaine
    Jan 4, 2007 at 6:00 pm
    Jan 10, 2007 at 8:18 pm
  • I have a query made by joining two subqueries where the outer query performing the join takes significantly longer to run than the two subqueries. The first subquery runs in 600ms. The seconds ...
    Jeremy HaileJeremy Haile
    Jan 10, 2007 at 4:22 pm
    Jan 10, 2007 at 7:30 pm
  • Hi, how can I monitor the size of the transaction log files using SQL Statements? Best Regards Silvio Silvio Ziegelwanger Research & Development Fabalabs Software GmbH Honauerstraße 4 4020 Linz ...
    Ziegelwanger, SilvioZiegelwanger, Silvio
    Jan 17, 2007 at 5:29 pm
    Jan 18, 2007 at 10:19 am
  • I have a table of messages with paths and inserted dates (among other things), like so: CREATE TABLE Messages ( msgkey BIGSERIAL PRIMARY KEY, path TEXT NOT NULL, inserted TIMESTAMP WITHOUT TIMEZONE ...
    TsuraanTsuraan
    Jan 9, 2007 at 11:05 pm
    Jan 10, 2007 at 5:14 pm
  • Hello, I am looking at upgrading from 8.1.2 to 8.2.0, and I've found a query which runs a lot slower. Here is the query: select type, currency_id, instrument_id, sum(amount) as total_amount from ...
    Dave DutcherDave Dutcher
    Jan 5, 2007 at 12:31 am
    Jan 8, 2007 at 6:59 pm
  • I'm curious what parameters you guys typically *always* adjust on new PostgreSQL installs. I am working with a database that contains several large tables (10-20 million) and many smaller tables ...
    Jeremy HaileJeremy Haile
    Jan 2, 2007 at 4:24 pm
    Jan 2, 2007 at 8:04 pm
  • Some of my very large tables (10 million rows) need to be analyzed by autovacuum on a frequent basis. Rather than specifying this as a percentage of table size + base threshold, I wanted to specify ...
    Jeremy HaileJeremy Haile
    Jan 18, 2007 at 6:37 pm
    Jan 18, 2007 at 9:53 pm
  • Hi, Can anybody tell me how can I implement data Caching in the shared memory using PostgreSQL. For one of the projects we are using Postgres version 8.0.3 and were planning to support table ...
    Ramachandra BhaskaramRamachandra Bhaskaram
    Jan 16, 2007 at 8:43 am
    Jan 16, 2007 at 5:04 pm
  • Hello, I have an authorization table that associates 1 customer IP to a service IP to determine a TTL (used by a radius server). table auth client varchar(15); service varchar(15); ttl int4; client ...
    Pomarede NicolasPomarede Nicolas
    Jan 29, 2007 at 4:22 pm
    Feb 1, 2007 at 7:30 pm
  • Hello, I discovered a query which is taking 70 seconds on 8.2.1 which used to take under a second on 8.1.2. I was digging into what was causing it and I believe the problem is a view which the ...
    Dave DutcherDave Dutcher
    Jan 23, 2007 at 10:08 pm
    Jan 30, 2007 at 2:54 pm
  • Hi all, I have postgresql 7.4.2 running on debian and I have the oddest postgresql behaviour I've ever seen. I do the following queries: espsm_asme=# select customer_app_config_id, ...
    ArnauArnau
    Jan 29, 2007 at 12:22 pm
    Jan 29, 2007 at 7:43 pm
  • PostgreSQL version: 8.2.1 OS: Windows Server 2003 I have a relatively simple query where the planner chooses a sequential scan when using the IN operator but chooses an index scan when using ...
    Ryan HolmesRyan Holmes
    Jan 27, 2007 at 11:35 pm
    Jan 28, 2007 at 2:32 am
  • Does anyone have experience with using postgres for data warehousing? Right, I saw one post suggestion to use mysql for a mostly read-only database ... but anyway, I think it's not a question to ...
    Tobias BroxTobias Brox
    Jan 23, 2007 at 12:49 pm
    Jan 24, 2007 at 3:21 am
  • Hello List, Not sure to which list I should post (gray lines, and all that), so point me in the right direction if'n it's a problem. I am in the process of learning some of the art/science of ...
    Kevin HunterKevin Hunter
    Jan 17, 2007 at 10:16 pm
    Jan 19, 2007 at 9:41 pm
  • Hi List, Can anybody help me out with this - is autovacuum similar to vacuum full analyse verbose. -- Regards Gauri
    Gauri KanekarGauri Kanekar
    Jan 18, 2007 at 1:24 pm
    Jan 18, 2007 at 2:14 pm
  • (now with a more sensible subject) I have been trying to change a many parameters on server versions 7.4.15, 8.1.4, 8.2.0 and 8.2.1. I still hope a have managed to keep my head straigth and that i do ...
    Rolf Østvik (HA/EXA)Rolf Østvik (HA/EXA)
    Jan 14, 2007 at 12:44 pm
    Jan 15, 2007 at 8:58 am
  • Well, once again I'm hosed because there's no way to tell the optimizer the cost for a user-defined function. I know this issue has already been raised (by me!) several times, but I have to remind ...
    Craig A. JamesCraig A. James
    Jan 4, 2007 at 3:10 am
    Jan 4, 2007 at 6:56 am
  • Hi List, Can anybody suggest some comprehensive test for version change from 8.1.3 to 8.2 -- Thanks in advance Gauri
    Gauri KanekarGauri Kanekar
    Jan 18, 2007 at 4:43 am
    Jan 18, 2007 at 5:35 am
  • Hi, I know that the problem with the following SQL is the "LOG.CODCEP = ENDE.CODCEP||CODLOG" condition, but what can I do to improve the performance? Is there a type of index that could help or is ...
    Carlos H. ReimerCarlos H. Reimer
    Jan 11, 2007 at 6:17 pm
    Jan 11, 2007 at 9:15 pm
  • I'm using pg_dump/pg_restore to quickly copy databases between servers. But my server keeps crashing when I run pg_restore: glibc detected *** double free or corruption (!prev): 0x0a00b1a0 Postgres: ...
    Craig A. JamesCraig A. James
    Jan 2, 2007 at 1:29 am
    Jan 2, 2007 at 6:11 am
  • I have been researching how to improve my overall performance of postgres. I am a little confused on the reasoning for how work-mem is used in the postgresql.conf file. The way I understand the ...
    Campbell, LanceCampbell, Lance
    Jan 28, 2007 at 8:18 pm
    Jan 28, 2007 at 10:11 pm
  • Hi List, When auto vacuum is over the dead tuple are seems to get reduced, but physical size of database do not decreases. We are using Postgres 8.1.3 and this are the auto vacuum settings. ...
    Gauri KanekarGauri Kanekar
    Jan 24, 2007 at 8:49 am
    Jan 24, 2007 at 9:14 am
  • Hi, We are having 3 tables; 1. persons <-- Base table and no data will be inserted in this table. 2. Person1 <-- Inherited table from persons all data will be inserted in this table. 3. PersonalInfo ...
    Ramachandra BhaskaramRamachandra Bhaskaram
    Jan 17, 2007 at 10:43 am
    Jan 22, 2007 at 9:42 am
  • Hi, Can anybody help me out to get following info of all the tables in a database. table_len tuple_count tuple_len tuple_percent dead_tuple_count dead_tuple_len dead_tuple_percent free_space ...
    Gauri KanekarGauri Kanekar
    Jan 16, 2007 at 1:48 pm
    Jan 17, 2007 at 6:37 am
  • Adam, This optimization would require teaching the planner to use an index for MAX/MIN when available. It seems like an OK thing to do to me. - Luke
    Luke LonerganLuke Lonergan
    Jan 15, 2007 at 7:41 am
    Jan 15, 2007 at 3:54 pm
  • Hello All, I am using the latest 8.2 source that I compiled with Sun Studio 11 and tested it on Solaris 10 11/06 against an app server. I find that the CPU utilization was higher than I expected and ...
    Jignesh ShahJignesh Shah
    Jan 13, 2007 at 11:38 pm
    Jan 14, 2007 at 3:08 am
  • Greetings, I've been running Postgresql for many years now and have been more than happy with its performance and stability. One of those things I've never quite understood was vacuuming. So I've ...
    Daryl HerzmannDaryl Herzmann
    Jan 6, 2007 at 7:26 pm
    Jan 6, 2007 at 7:46 pm
  • Hello, we recently migrated our system from 8.1.x to 8.2 and when running dumps have noticed an extreme decrease in speed where the dump is concerned (by more than a factor of 2). I was wondering if ...
    Erik JonesErik Jones
    Jan 2, 2007 at 4:42 pm
    Jan 2, 2007 at 5:40 pm
  • If I set work-mem at a particular amount of memory how do I answer the following questions: 1) How many of my queries were able to run inside the memory I allocated for work-mem? 2) How many of my ...
    Campbell, LanceCampbell, Lance
    Jan 29, 2007 at 3:07 am
    Jan 29, 2007 at 2:54 pm
  • Hello all, I have a setup in which four client machines access a Postgres database (8.1.1) (on a Linux box). So, there are connections from each machine to the database; hence, the Linux box has ...
    Subramaniam AiylamSubramaniam Aiylam
    Jan 23, 2007 at 4:11 pm
    Jan 24, 2007 at 8:49 am
  • Did anybody get a chance to look at this? Is it expected behavior? Everyone seemed so incredulous, I hoped maybe this exposed a bug that would be fixed in a near release. -----Original Message----- ...
    Adam RichAdam Rich
    Jan 15, 2007 at 4:51 am
    Jan 15, 2007 at 10:36 am
  • 12345678901234567890123456789012345678901234567890123456789012345678901234567890 00000000001111111111222222222233333333334444444444555555555566666666667777777777 I have been trying to change a many ...
    Rolf Østvik (HA/EXA)Rolf Østvik (HA/EXA)
    Jan 14, 2007 at 12:31 pm
    Jan 14, 2007 at 1:46 pm
  • Hi, Carlos, Wouldn't it be better if you used INT in 'codcep' in both tables (as CEP/ZIP numbers are [0-9]{8})? Casting as Tom Lane suggested is also a good alternative, yet I think it'd be much ...
    Cesar SugaCesar Suga
    Jan 11, 2007 at 11:48 pm
    Jan 12, 2007 at 5:37 pm
Jan 2007
12
Group Navigation
period‹ prev | Jan 2007 | next ›
Group Overview
grouppgsql-performance @
categoriespostgresql
discussions58
posts451
users114
websitepostgresql.org
irc#postgresql

114 users for January 2007

Tom Lane: 45 posts Jeremy Haile: 35 posts Jim Nasby: 22 posts Scott Marlowe: 13 posts Erik Jones: 12 posts Richard Huxton: 12 posts Adam Rich: 10 posts Dave Cramer: 10 posts Dave Dutcher: 10 posts Luke Lonergan: 10 posts Chad Wagner: 9 posts Simon Riggs: 9 posts Steve: 9 posts Alvaro Herrera: 8 posts Craig A. James: 8 posts Joshua D. Drake: 8 posts Rolf Østvik: 7 posts Tobias Brox: 7 posts Gauri Kanekar: 6 posts Heikki Linnakangas: 6 posts
show more
Archives