FAQ

PostgreSQL > pgsql-performance > April 2005

Search Discussions

81 discussions - 760 posts

  • Folks, Params: PostgreSQL 8.0.1 on Solaris 10 Statistics = 500 (tablenames have been changed to protect NDA) e1=# select tablename, null_frac, correlation, n_distinct from pg_stats where tablename = ...
    Josh BerkusJosh Berkus
    Apr 19, 2005 at 7:04 pm
    May 4, 2005 at 2:52 am
  • Just curious, but does anyone have an idea of what we are capable of? I realize that size of record would affect things, as well as hardware, but if anyone has some ideas on max, with 'record size', ...
    Marc G. FournierMarc G. Fournier
    Apr 1, 2005 at 6:06 pm
    Apr 6, 2005 at 8:16 am
  • Sorry if you feel I am lashing out at a community. Just to say it again, I am very appreciative of all the help everyone has supplied. I am running on more then just the 4 proc Dell (in fact my tests ...
    Joel FradkinJoel Fradkin
    Apr 20, 2005 at 7:52 pm
    Apr 27, 2005 at 9:01 pm
  • I think drive B is clearly the best choice. Matt said "all other features are the same", including price. I take that to mean that the seek time and throughput are also identical. However, I think ...
    Dave HeldDave Held
    Apr 14, 2005 at 6:21 pm
    Apr 21, 2005 at 2:50 am
  • All, Running PostgreSQL 7.4.2, Solaris. Client is reporting that the size of an index is greater than the number of rows in the table (1.9 million vs. 1.5 million). Index was automatically created ...
    Bill ChandlerBill Chandler
    Apr 21, 2005 at 5:00 pm
    Apr 23, 2005 at 3:12 am
  • I am new to cross references between tables, and I am trying to understand how they impact performance. From reading the documentation I was under the impression that deffering foreign keys would ...
    Richard van den BergRichard van den Berg
    Apr 14, 2005 at 12:00 pm
    Apr 20, 2005 at 3:27 pm
  • Our vendor is trying to sell us on an Intel SRCS16 SATA raid controller instead of the 3ware one. Poking around it seems this does come with Linux drivers and there is a battery backup option. So it ...
    Greg StarkGreg Stark
    Apr 14, 2005 at 2:54 pm
    May 12, 2005 at 2:26 am
  • Hi list, I noticed on a forum a query taking a surprisingly large amount of time in MySQL. Of course I wanted to prove PostgreSQL 8.0.1 could do it much better. To my surprise PostgreSQL was ten ...
    Arjen van der MeijdenArjen van der Meijden
    Apr 6, 2005 at 4:52 pm
    Apr 8, 2005 at 12:30 am
  • Hi everybody, One of our clients was using SQL-Server and decided to switch to PostgreSQL 8.0.1. Hardware: Dual processor Intel(R) Xeon(TM) CPU 3.40GHz OS: Enterprise Linux with 2.6.9-5 SMP kernel ...
    Shoaib Burq (VPAC)Shoaib Burq (VPAC)
    Apr 21, 2005 at 11:50 am
    Apr 26, 2005 at 3:05 pm
  • Hi, I'm having a pretty serious problem with postgresql's performance. Currently, I have a cron task that is set to restart and vacuumdb -faz every six hours. If that doesn't happen, the disk goes ...
    Richard PlotkinRichard Plotkin
    Apr 20, 2005 at 7:28 pm
    Apr 25, 2005 at 3:14 am
  • Hi folks, I like to use (immutable) functions for looking up serveral (almost constant) things, i.e fetching a username by id. This makes my queries more clear. But is this really performant ? Lets ...
    Enrico WeigeltEnrico Weigelt
    Apr 15, 2005 at 8:55 pm
    Apr 22, 2005 at 10:15 am
  • I must be missing something important, because I am just not seeing why this query is slower on a 4 processor 8 gig machine running redhat AS4. The SQL: explain analyze SELECT a.clientnum, ...
    Joel FradkinJoel Fradkin
    Apr 13, 2005 at 2:40 pm
    Apr 18, 2005 at 1:31 pm
  • Hi, I thought that an index can be used for sorting. I'm a little confused about the following result: create index OperationsName on Operations(cOperationName); explain SELECT * FROM Operations ...
    Andrei GasparAndrei Gaspar
    Apr 18, 2005 at 3:30 pm
    May 14, 2005 at 1:37 pm
  • I spent a great deal of time over the past week looking seriously at Postgres and MYSQL. Objectively I am not seeing that much of an improvement in speed with MYSQL, and we have a huge investment in ...
    Joel FradkinJoel Fradkin
    Apr 27, 2005 at 3:01 pm
    Apr 28, 2005 at 7:39 am
  • We have a table with 1M rows that contain sessions with a start and finish timestamps. When joining this table with a 10k table with rounded timestamps, explain shows me sequential scans are used, ...
    Richard van den BergRichard van den Berg
    Apr 20, 2005 at 2:22 pm
    Apr 22, 2005 at 9:04 am
  • Folks, I'm wondering if it might be useful to be able to add estimated selectivity to a function definition for purposes of query estimation. Currently function scans automatically return a flat ...
    Josh BerkusJosh Berkus
    Apr 8, 2005 at 10:10 pm
    Apr 11, 2005 at 1:30 am
  • All, A couple of questions regarding REINDEX command: Running PostgreSQL 7.4.2 on Solaris. 1) When is it necessary to run REINDEX or drop/create an index? All I could really find in the docs is: "In ...
    Bill ChandlerBill Chandler
    Apr 18, 2005 at 7:21 pm
    Apr 19, 2005 at 6:34 pm
  • Hi all, I've just noticed an interesting behaviour with PGSQL. My software is made up of few different modules that interact through PGSQL database. Almost every query they do is an individual ...
    Slavisa GaricSlavisa Garic
    Apr 13, 2005 at 2:29 am
    Apr 14, 2005 at 1:15 am
  • Running this explain on windows box, but production on linux both 8.0.1 The MSSQL is beating me out for some reason on this query. The linux box is much more powerful, I may have to increase the ...
    Joel FradkinJoel Fradkin
    Apr 7, 2005 at 3:13 pm
    Apr 9, 2005 at 3:17 pm
  • Hello, I am trying to understand what I need to do for this system to stop using swap. Maybe it's something simple, or obvious for the situation. I'd appreciate some thoughts/suggestions. Some ...
    Anjan DaveAnjan Dave
    Apr 27, 2005 at 5:48 pm
    Apr 28, 2005 at 12:13 pm
  • I'm in the fortunate position of having a newly built database server that's pre-production. I'm about to run it through the ringer with some simulations of business data and logic, but I wanted to ...
    Thomas F.O'ConnellThomas F.O'Connell
    Apr 15, 2005 at 9:02 pm
    Apr 26, 2005 at 5:50 pm
  • Unfortunately. But we are in the the process to choose Postgresql with pgcluster. I'm currently running some tests (performance, stability...) Save the money on the license fees, you get it for your ...
    BsimonBsimon
    Apr 6, 2005 at 7:10 am
    Apr 8, 2005 at 4:53 am
  • Hi all, Ia a Guy from Germany an a strong Postgres believer! It is the best OpenSource Database i have ever have bee tasted and i try to using it in any Database Environments. It is exiting to see ...
    Apoc9009Apoc9009
    Apr 26, 2005 at 8:09 am
    Apr 27, 2005 at 12:42 am
  • I'd like to create a fail-over server in case of a problem. Ideally, it would be synchronized with our main database server, but I don't see any major problem with having a delay of up to 4 hours ...
    Matthew NuzumMatthew Nuzum
    Apr 12, 2005 at 4:25 pm
    Apr 13, 2005 at 8:03 am
  • Hi All, Thanks to all on the NOVICE list that gave me help I now have a query running that returns the results I am after. :-) Now of course I want it to run faster. Currently it clocks in at ~160ms. ...
    Keith WorthingtonKeith Worthington
    Apr 7, 2005 at 2:17 pm
    Apr 12, 2005 at 6:22 pm
  • Hi all again, My next queststion is dedicated to blobs in my Webapplication (using Tomcat 5 and JDBC integrated a the J2EE Appserver JBoss). Filesystems with many Filesystem Objects can slow down the ...
    Apoc9009Apoc9009
    Apr 26, 2005 at 8:23 am
    May 12, 2005 at 10:03 am
  • Err, yes. But that's not quite the same as core telling us the current driver is being replaced. Regards, Dave.
    Dave PageDave Page
    Apr 27, 2005 at 7:47 pm
    Apr 28, 2005 at 3:44 am
  • A friend of mine has an application where he's copying in 4000 rows at a time into a table that has about 4M rows. Each row is 40-50 bytes. This is taking 25 seconds on a dual PIII-1GHz with 1G of ...
    Jim C. NasbyJim C. Nasby
    Apr 20, 2005 at 2:00 am
    Apr 20, 2005 at 10:23 pm
  • Sorry to blend threads, but in my kinda longish, somewhat thankless, essentially anonymous, and quite average career as a dba, I have found that the 7K would be best spent on a definitive end-to-end ...
    Mohan, RossMohan, Ross
    Apr 15, 2005 at 4:18 pm
    Apr 20, 2005 at 3:25 pm
  • Hi all, Some months ago i post a similar problem here i it was solved by running vaccumdb time by time. So, when i started using the postgres, i never been used the vacuumdb, and after 2 months i ...
    Rodrigo MorenoRodrigo Moreno
    Apr 18, 2005 at 2:36 pm
    Apr 20, 2005 at 12:35 am
  • Hello. I'm trying to restore my database from dump in several parrallel processes, but restore process works too slow. Number of rows about 100 000 000, RAM: 8192M CPU: Ultra Sparc 3 Number of CPU: 4 ...
    Nurlan Mukhanov (AL/EKZ)Nurlan Mukhanov (AL/EKZ)
    Apr 18, 2005 at 4:52 am
    Apr 19, 2005 at 3:06 pm
  • Maybe better for -hackers, but here it goes anyway... Has anyone looked at compressing WAL's before writing to disk? On a system generating a lot of WAL it seems there might be some gains to be had ...
    Jim C. NasbyJim C. Nasby
    Apr 5, 2005 at 4:05 am
    Apr 18, 2005 at 6:32 pm
  • Hi, i am thinking about swiching to plperl as it seems to me much more flexible and easier to create functions. what is the recommended PL for postgres? or which one is most widely used / most ...
    AlexAlex
    Apr 15, 2005 at 11:47 am
    Apr 17, 2005 at 11:48 pm
  • Hello, I am having a bit of trouble updating a single integer column. My table has around 10 columns and 260 000 records. update no.records set uid = 2; (uid is an integer. It has a btree index) This ...
    Bendik R.JohansenBendik R.Johansen
    Apr 12, 2005 at 2:12 pm
    Apr 12, 2005 at 3:40 pm
  • hi i'm not totally sure i should ask on this mailing list - so if you think i should better ask someplace else, please let me know. the problem i have is that specific queries (inserts and updates) ...
    Hubert lubaczewskiHubert lubaczewski
    Apr 12, 2005 at 10:47 am
    Apr 12, 2005 at 3:00 pm
  • On our production server, I can insert 5000 tuples in 2100 ms. Single Xeon 2.6 Ghz 2 Gigs ram 3ware RAID 5 SATA drives array, 3 drives only :-(( PG 8.0 - fsync off I do think inserting 5000 tuples in ...
    BsimonBsimon
    Apr 6, 2005 at 5:02 pm
    Apr 6, 2005 at 7:02 pm
  • Anybody a solution for the next problem: people can subscribe to a service for 1 or more days (upto a max. of 365). So in the database is stored: first_date and last_date To select which people are ...
    H.J. SandersH.J. Sanders
    Apr 1, 2005 at 10:05 am
    Apr 2, 2005 at 1:57 pm
  • I have this query that takes a little over 8 min to run: select client,max(atime) as atime from usage_access where atime = (select atime - '1 hour'::interval from usage_access order by atime desc ...
    Matthew NuzumMatthew Nuzum
    Apr 26, 2005 at 8:17 pm
    Apr 26, 2005 at 10:33 pm
  • hi, I am using psql 7.1.3 I didn't find option analyse in explain command.. how to get time taken by SQL procedure/query? regards, stp..
    S.Thanga PrakashS.Thanga Prakash
    Apr 7, 2005 at 7:42 am
    Apr 8, 2005 at 1:30 am
  • Hi, I have just upgraded our db from 7.4.2 to 8.0.1 and we are doing some testing. For some reasons, we have discovered that our application performs much slower on 8.0.1. My initial reaction was to ...
    Alvin YkAlvin Yk
    Apr 4, 2005 at 10:32 am
    Apr 4, 2005 at 5:05 pm
  • Hi folks, I'm doing a simple lookup in a small table by an unique id, and I'm wondering, why explains tells me seqscan is used instead the key. The table looks like: id bigint primary key, a varchar, ...
    Enrico WeigeltEnrico Weigelt
    Apr 21, 2005 at 7:05 pm
    Apr 21, 2005 at 9:58 pm
  • Dear Postgres Masters: We are using postgres 7.4 in our java application on RedHat linux. The Java application connects to Postgres via JDBC. The application goes through a 'discovery' phase, whereas ...
    Shachindra AgarwalShachindra Agarwal
    Apr 20, 2005 at 6:49 pm
    Apr 20, 2005 at 11:35 pm
  • Hi all, Just wanted everyone to know what we're pulling CVS HEAD nightly so it can be tested in STP now. Let me know if you have any questions. Tests are not automatically run yet, but I hope to ...
    Mark WongMark Wong
    Apr 13, 2005 at 6:11 pm
    Apr 19, 2005 at 10:14 pm
  • What are the statistics for tbljobtitle.id and tbljobtitle.clientnum I added default_statistics_target = 250 to the config and re-loaded the data base. If that is what you mean? --- how many distinct ...
    Joel FradkinJoel Fradkin
    Apr 18, 2005 at 1:00 pm
    Apr 18, 2005 at 2:31 pm
  • My server is crashing on a delete statement. Here's the error message in the log file: LOCATION: ShutdownXLOG, xlog.c:3090 LOG: 00000: database system is shut down LOCATION: ShutdownXLOG, xlog.c:3104 ...
    Don DrakeDon Drake
    Apr 11, 2005 at 3:16 am
    Apr 11, 2005 at 3:15 pm
  • I wish I had a Dell system and run case to show you Alex, but I don't... however...using Oracle's "direct path" feature, it's pretty straightforward. We've done 110,000 rows per second into ...
    Mohan, RossMohan, Ross
    Apr 6, 2005 at 4:13 pm
    Apr 6, 2005 at 4:44 pm
  • I have a query in my application that takes an unreasonable amount of time to complete ( 4.5 hours execution time). After reviewing the EXPLAIN and EXPLAIN ANALYZE output for that and similar ...
    Peterson, BjornPeterson, Bjorn
    Apr 1, 2005 at 4:17 pm
    Apr 1, 2005 at 5:27 pm
  • I've ported enough of my companies database to Postgres to make warehousing on PG a real possibility. I thought I would toss my data migration architecture ideas out for the list to shoot apart.. 1. ...
    Richard RowellRichard Rowell
    Apr 27, 2005 at 4:07 pm
    Apr 28, 2005 at 3:01 pm
  • If I have a freshly CLUSTERed table and queries that want to do a merge join, it seems to me that quite a bit of time is spent unnecessarily sorting the already-sorted table. An example such query I ...
    Ron MayerRon Mayer
    Apr 25, 2005 at 8:10 pm
    Apr 26, 2005 at 5:42 am
  • Hi, I have a series of tables with identical structure. Some contain a few thousand rows and some contain 3,000,000 rows. Another applicate writes the rows and my applicate reads then just by ...
    David RousselDavid Roussel
    Apr 21, 2005 at 10:13 am
    Apr 21, 2005 at 6:04 pm
Apr 2005
12
Group Navigation
period‹ prev | Apr 2005 | next ›
Group Overview
grouppgsql-performance @
categoriespostgresql
discussions81
posts760
users127
websitepostgresql.org
irc#postgresql

127 users for April 2005

Tom Lane: 86 posts Josh Berkus: 44 posts Alex Turner: 38 posts Joel Fradkin: 33 posts Jim C. Nasby: 29 posts John Arbash Meinel: 20 posts Greg Stark: 19 posts Mohan, Ross: 19 posts Dave Held: 18 posts Christopher Petrilli: 17 posts Richard van den Berg: 17 posts Simon Riggs: 15 posts Christopher Kings-Lynne: 14 posts Joshua D. Drake: 14 posts Mischa Sandberg: 13 posts Richard Plotkin: 12 posts Steve Poe: 12 posts Bruce Momjian: 11 posts Kevin Brown: 10 posts Pierre-Frédéric Caillaud: 10 posts
show more
Archives