Re: [PERFORM] large tables and simple = constant queries using indexes

Hi John, You don't use the same 'gene_ref ='-value, so its not a perfect comparison. And obviously, there is the fact that the data can be in the disk cache, the second time you run it, which would explain the almost instantaneous result for the second query. If repeating the query a few

Re: [PERFORM] large tables and simple = constant queries using indexes

Perfect - thanks Arjen. Using your value of 200 decreased the time to 15 seconds, and using a value of 800 makes it almost instantaneous. I'm really not concerned about space usage; if having more statistics increases performance this much, maybe I'll just default it to 1000? Strangely,

Re: [PERFORM] varchar index joins not working?

Adam Gundy wrote: I'm hitting an unexpected problem with postgres 8.3 - I have some tables which use varchar(32) for their unique IDs which I'm attempting to join using some simple SQL: select * from group_access, groups where group_access.groupid = groups.groupid and group_access.uid

Re: [PERFORM] large tables and simple = constant queries using indexes

On Thu, 10 Apr 2008, PFC wrote: ... Lots of useful advice ... - If you often query rows with the same gene_ref, consider using CLUSTER to physically group those rows on disk. This way you can get all rows with the same gene_ref in 1 seek instead of 2000. Clustered tables also make Bitmap

Re: [PERFORM] large tables and simple = constant queries using indexes

Thanks a lot, all of you - this is excellent advice. With the data clustered and statistics at a more reasonable value of 100, it now reproducibly takes even less time - 20-57 ms per query. After reading the section on Statistics Used By the Planner in the manual, I was a little concerned

[PERFORM] Creating large database of MD5 hash values

Hello, I am creating a large database of MD5 hash values. I am a relative newb with PostgreSQL (or any database for that matter). The schema and operation will be quite simple -- only a few tables, probably no stored procedures -- but I may easily end up with several hundred million rows of hash

Re: [PERFORM] varchar index joins not working?

Richard Huxton wrote: Adam Gundy wrote: I'm hitting an unexpected problem with postgres 8.3 - I have some tables which use varchar(32) for their unique IDs which I'm attempting to join using some simple SQL: select * from group_access, groups where group_access.groupid = groups.groupid and

Re: [PERFORM] large tables and simple = constant queries using indexes

On Apr 10, 2008, at 9:44 AM, John Beaver wrote: Thanks a lot, all of you - this is excellent advice. With the data clustered and statistics at a more reasonable value of 100, it now reproducibly takes even less time - 20-57 ms per query. After reading the section on Statistics Used By the

Re: [PERFORM] Performance with temporary table

I see, I am having practically the same problem... utilizing partitioning idea http://www.postgresql.org/docs/8.3/interactive/ddl-partitioning.html by table inheritance. I have prepared a post with some trigger and rule examples for you

Re: [PERFORM] large tables and simple = constant queries using indexes

Thanks Eric and Gaestano - interesting, and both examples of my naivite. :) I tried running large select(*) queries on other tables followed by another try at the offending query, and it was still fast. Just to be absolutely sure this is a scalable solution, I'll try restarting my computer in

Re: [PERFORM] large tables and simple = constant queries using indexes

In response to John Beaver [EMAIL PROTECTED]: Thanks Eric and Gaestano - interesting, and both examples of my naivite. :) I tried running large select(*) queries on other tables followed by another try at the offending query, and it was still fast. Just to be absolutely sure this is a

Re: [PERFORM] large tables and simple = constant queries using indexes

On Thu, 10 Apr 2008, Bill Moran wrote: If you install the pg_buffercache addon, you can actually look into PostgreSQL's internals and see what tables are in the buffer in real time. The Inside the PostgreSQL Buffer Cache talk I did at the recent East conference is now on-line at

Re: [PERFORM] varchar index joins not working?

On Thu, Apr 10, 2008 at 8:52 AM, Adam Gundy [EMAIL PROTECTED] wrote: Richard Huxton wrote: How many distinct values do you have in groups.groupid and group_access.group_id? for the small database (since it shows the same problem): group_access: 280/268 groups: 2006/139 for the

[PERFORM] Partitioned tables - planner wont use indexes

Hi List; I'm having some performance issues with a partitioned table. We have a VERY large table that we've partitioned by day. Currently we have 17 partitions - each partition table contains 700million rows. One of the things we need to query is the min date from the master table - we

Re: [PERFORM] recommendations for web/db connection pooling or DBD::Gofer reviews

Under heavy load, Apache has the usual failure mode of spawning so many threads/processes and database connections that it just exhausts all the memory on the webserver and also kills the database. As usual, I would use lighttpd as a frontend (also serving static files) to handle the

Re: [PERFORM] large tables and simple = constant queries using indexes

Thanks a lot, all of you - this is excellent advice. With the data clustered and statistics at a more reasonable value of 100, it now reproducibly takes even less time - 20-57 ms per query. 1000x speedup with proper tuning - always impressive, lol. IO seeks are always your

Re: [PERFORM] large tables and simple = constant queries using indexes

John Beaver wrote: - Trying the same constant a second time gave an instantaneous result, I'm guessing because of query/result caching. AFAIK no query/result caching is in place in postgres, what you are experiencing is OS disk/memory caching. Regards Gaetano Mendola -- Sent via

[PERFORM] Performance is low Postgres+Solaris

Hi All, We are using solaris 10 x86/AMD Opteron and postgresql 8.2 on SunFire X2100 , however performance is very slow in contrast to linux debian in the same platform. Is this normal? Thanks Regards Mahi

Re: [PERFORM] Looking for bottleneck during load test

Hell, Robert wrote: I tried different other tools for random IO (including a self written one which does random lseek and read). This tool, started during one of our tests, achieves 2 iops (8k each). Started alone I get something about 1,500 iops with an avg latency of 100 ms. 1500 iops

Re: [PERFORM] Views and functions returning sets of records

Can we write retrieving only 10 records from 4000 records plz tell me asap On Mar 23, 8:28 pm, [EMAIL PROTECTED] (Tom Lane) wrote: Giorgio Valoti [EMAIL PROTECTED] writes: Are there any way to pass some hints to the planner? For example, could the IMMUTABLE/STABLE/VOLATILE modifiers be of

[PERFORM] shared_buffers in 8.2.x

Hi all, specifing as shared_buffers = 26800 in 8.2.x will this value accepted like in the 8.1.x series and then 26800*8192 bytes = 209 MB or 26800 bytes (not being specified the memory unit)? Regards Gaetano Mendola -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)

Re: [PERFORM] shared_buffers in 8.2.x

On Apr 10, 2008, at 7:39 AM, Gaetano Mendola wrote: Hi all, specifing as shared_buffers = 26800 in 8.2.x will this value accepted like in the 8.1.x series and then 26800*8192 bytes = 209 MB or 26800 bytes (not being specified the memory unit)? With no specified unit then it defaults to 8K.

Re: [PERFORM] Performance is low Postgres+Solaris

MUNAGALA REDDY wrote: Hi All, We are using solaris 10 x86/AMD Opteron and postgresql 8.2 on SunFire X2100 , however performance is very slow in contrast to linux debian in the same platform. Is this normal? Thanks Regards Mahi