Re: [PERFORM] Slow count(*) again...
On Wednesday 13 October 2010 06:27:34 you wrote: > On Wed, Oct 13, 2010 at 4:38 AM, Neil Whelchel wrote: > > There seems to be allot of discussion about VACUUM FULL, and its > > problems. The overall buzz seems to be that VACUUM FULL is a bad idea (I > > could be wrong here). It has been some time since I have read the > > changelogs, but I seem to remember that there have been some major > > changes to VACUUM FULL recently. Maybe this needs to be re-visited in > > the documentation. > > In 9.0, VACUUM FULL does something similar to what CLUSTER does. This > is a much better idea than what it did in 8.4 and prior. > > > crash:~# time psql -U test test -c "VACUUM FULL log;" > > VACUUM > > > > real4m49.055s > > user0m0.000s > > sys 0m0.000s > > > > crash:~# time psql -U test test -c "SELECT count(*) FROM log;" > > count > > -- > > 10050886 > > (1 row) > > > > real0m9.665s > > user0m0.000s > > sys 0m0.004s > > > > A huge improvement from the minute and a half before the VACUUM FULL. > > This is a very surprising result that I would like to understand > better. Let's assume that your UPDATE statement bloated the table by > 2x (you could use pg_relation_size to find out exactly; the details > probably depend on fillfactor which you might want to lower if you're > going to do lots of updates). That ought to mean that count(*) has to > grovel through twice as much data, so instead of taking 9 seconds it > ought to take 18 seconds. Where the heck is the other 1:12 going? > This might sort of make sense if the original table was laid out > sequentially on disk and the updated table was not, but how and why > would that happen? This is likely due to the table not fitting in memory before the VACUUM FULL. I am glad that you suggested using pg_relation_size, I somehow didn't think of it at the time. I will redo the test and publish the results of pg_relation_size. -Neil- -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Bogus startup cost for WindowAgg
Ants Aasma writes: > Seems that the window aggregate node doesn't take into account that it > has to consume the whole input before outputting the first row. Well, the reason it doesn't assume that is it's not true ;-). In this particular case it's true, but more generally you only have to read the current input partition, and often not even all of that. I'm not sure offhand how much intelligence would have to be added to make a reasonable estimate of the effects of having to read ahead of the current input row, but it's probably not trivial. We haven't spent much time at all yet on creating a realistic cost model for WindowAgg... regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow count(*) again...
On 13/10/10 21:44, Mladen Gogala wrote: On 10/13/2010 3:19 AM, Mark Kirkwood wrote: I think that major effect you are seeing here is that the UPDATE has made the table twice as big on disk (even after VACUUM etc), and it has gone from fitting in ram to not fitting in ram - so cannot be effectively cached anymore. In the real world, tables are larger than the available memory. I have tables of several hundred gigabytes in size. Tables shouldn't be "effectively cached", the next step would be to measure "buffer cache hit ratio", tables should be effectively used. Sorry Mladen, I didn't mean to suggest that all tables should fit into ram... but was pointing out (one reason) why Neil would expect to see a different sequential scan speed after the UPDATE. I agree that in many interesting cases, tables are bigger than ram [1]. Cheers Mark [1] Having said that, these days 64GB of ram is not unusual for a server... and we have many real customer databases smaller than this where I work. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow count(*) again...
On 13/10/10 23:16, Neil Whelchel wrote: The good ol' bruit force approach! I knew I'd see this one sooner or later. Though I was not sure if I was going to see the 16TB of RAM suggestion first. Seriously though, as the title of this thread suggests, everything is relative. Sure count(*) and everything else will work faster with more system power. It just seems to me that count(*) is slower than it could be given a set of conditions Since the thread has started, I have had people ask about different system configurations, especially the filesystem (XFS, ext4...). I have never tested ext4, and since we are all involved here, I thought that I could do so and share my results for others, that is why I got into time testing stuff. Time testing count(*) in my later postings is really not the point as count is simply dragging the entire table off of the RAID through RAM, I can use any other function like max()... No that can narrow down its scan with an INDEX... Ok, sum(), there we go! Well in some (quite common) use cases, the queries cannot be known in advance, and the tables are considerably bigger than ram... this makes the fast IO a good option - sometimes better (and in the end cheaper) than trying to maintain every conceivable covering index. Of course it would be great if Postgres could use the indexes alone to execute certain queries - we may see some of that capability in the next few release (keep and eye on messages concerning the 'Visibility Map'). regards Mark -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Bogus startup cost for WindowAgg
Ants Aasma wrote: I hit an issue with window aggregate costing while experimenting with providing a count of the full match along side a limited result set. Seems that the window aggregate node doesn't take into account that it has to consume the whole input before outputting the first row. When this is combined with a limit, the resulting cost estimate is wildly underestimated, leading to suboptimal plans. What is your histogram size? That's defined by the default_statistics_target in your postgresql.conf. Check the column histograms like this: news=> select attname,array_length(most_common_vals,1) from pg_stats where tablename='moreover_documents_y2010m09'; attname| array_length --+-- document_id | dre_reference| headline | 1024 author | 212 url | rank | 59 content | 1024 stories_like_this| internet_web_site_id | 1024 harvest_time | 1024 valid_time | 1024 keyword | 95 article_id | media_type |5 source_type |1 created_at | 1024 autonomy_fed_at | 1024 language | 37 (18 rows) news=> show default_statistics_target; default_statistics_target --- 1024 (1 row) You will see that for most of the columns, the length of the histogram array corresponds to the value of the default_statistics_target parameter. For those that are smaller, the size is the total number of values in the column in the sample taken by the "analyze" command. The longer histogram, the better plan. In this case, the size does matter. Note that there are no histograms for the document_id and dre_reference columns. Those are the primary and unique keys, the optimizer can easily guess the distribution of values. -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 http://www.vmsinfo.com The Leader in Integrated Media Intelligence Solutions -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow count(*) again...
On Wed, Oct 13, 2010 at 1:59 PM, Jesper Krogh wrote: > On 2010-10-13 15:28, Robert Haas wrote: >> >> On Wed, Oct 13, 2010 at 6:16 AM, Neil Whelchel >> wrote: >> >>> >>> I might go as far as to rattle the cage of the developers to see if it >>> makes >>> any sense to add some column oriented storage capability to Postgres. >>> That >>> would be the hot ticket to be able to specify an attribute on a column so >>> that >>> the back end could shadow or store a column in a column oriented table so >>> aggregate functions could work on them with good efficiency, or is that >>> an >>> INDEX? >>> >> >> I'd love to work on that, but without funding it's tough to find the >> time. It's a big project. >> > > Is it hugely different from just getting the visibillity map suitable > for doing index-only scans and extracting values from the index > directly as Heikki has explained?] I think that there's a lot more to a real column-oriented database than index-only scans, although, of course, index-only scans are very important. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow count(*) again...
Could this be an interesting test use of https://www.fossexperts.com/ ? 'Community' driven proposal - multiple people / orgs agree to pay various portions? Maybe with multiple funders a reasonable target fund amount could be reached. Just throwing around ideas here. Mark -Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Robert Haas Sent: Wednesday, October 13, 2010 7:29 AM To: Neil Whelchel Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Slow count(*) again... On Wed, Oct 13, 2010 at 6:16 AM, Neil Whelchel wrote: > I might go as far as to rattle the cage of the developers to see if it makes > any sense to add some column oriented storage capability to Postgres. That > would be the hot ticket to be able to specify an attribute on a column so that > the back end could shadow or store a column in a column oriented table so > aggregate functions could work on them with good efficiency, or is that an > INDEX? I'd love to work on that, but without funding it's tough to find the time. It's a big project. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Bogus startup cost for WindowAgg
On Wed, Oct 13, 2010 at 10:35 PM, Mladen Gogala wrote: > You will see that for most of the columns, the length of the histogram array > corresponds to the value of the default_statistics_target parameter. For > those that are smaller, the size is the total number of values in the column > in the sample taken by the "analyze" command. The longer histogram, the > better plan. In this case, the size does matter. The issue here isn't that the statistics are off. The issue is, as Tom said, that the optimizer doesn't consider them for the cost model of the window aggregate. The trivial case I put forward wouldn't be too hard to cover - if there's no partitioning of the window and the frame is over the full partition, the startup cost should be nearly the same as the full cost. But outside of the trick I tried, I'm not sure if the trivial case matters much. I can also see how the estimation gets pretty hairy when partitioning, frames and real window functions come into play. One idea would be to cost three different cases. If the aggregate needs to read ahead some most likely constant number of rows, i.e. is not using an unbounded following frame, leave the startup cost as is. If there is partitioning, estimate the number of groups produced by the partitioning and add one n-th of the difference between startup and total cost. Otherwise, if the frame is to the end of the partition and there is no partitioning, set the startup cost equal to total cost, or in terms of the previous case, n=1. I don't know how accurate estimating the number of groups would be, or even if it is feasible to do it. If those assumptions hold, then it seems to me that this method should at-least cover any large O(n) effects. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] SQL functions vs. PL/PgSQL functions
On Wed, Oct 13, 2010 at 10:14 AM, Tom Lane wrote: > It's possible that at some point we'll try to introduce plan caching > for non-inlined SQL functions. hm, I think the search_path/function plan issue would have to be dealt with before doing this -- a while back IIRC you suggested function plans might be organized around search_path setting at plan time, or this would break a fair amount of code (for example, mine) :-). merlin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] SQL functions vs. PL/PgSQL functions
Merlin Moncure writes: > On Wed, Oct 13, 2010 at 10:14 AM, Tom Lane wrote: >> It's possible that at some point we'll try to introduce plan caching >> for non-inlined SQL functions. > hm, I think the search_path/function plan issue would have to be dealt > with before doing this -- Yeah, perhaps. There doesn't seem to be any groundswell of demand for doing anything about that anyway. Particularly since plpgsql is now installed by default, a reasonable answer to "I'd like the system to cache plans for this" is now "so write it in plpgsql instead". regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow count(*) again...
On 2010-10-14 06:22, mark wrote: Could this be an interesting test use of https://www.fossexperts.com/ ? 'Community' driven proposal - multiple people / orgs agree to pay various portions? Maybe with multiple funders a reasonable target fund amount could be reached. I might convince my boss to chip in... but how do we get the task up there.. should we find one to give an estimate first? -- Jesper -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] How does PG know if data is in memory?
2010/10/13 Ron Mayer : > Kevin Grittner wrote: >> >> ...Sybase named caches...segment off portions of the memory for >> specific caches... bind specific database >> objects (tables and indexes) to specific caches. ... >> >> When I posted to the list about it, the response was that LRU >> eviction was superior to any tuning any human would do. I didn't >> and don't believe that >> >> FWIW, the four main reasons for using it were: >> (1) Heavily used data could be kept fully cached in RAM... > > Lightly-used-but-important data seems like another use case. > > LRU's probably far better than me at optimizing for the total > throughput and/or average response time. But if there's a > requirement: > "Even though this query's very rare, it should respond > ASAP, even at the expense of the throughput of the rest > of the system." > it sounds like this kind of hand-tuning might be useful. it is exactly one of the purpose of pgfincore : http://villemain.org/projects/pgfincore#load_a_table_or_an_index_in_os_page_cache > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] oracle to psql migration - slow query in postgres
We are in the process of testing migration of our oracle data warehouse over to postgres. A potential showstopper are full table scans on our members table. We can't function on postgres effectively unless index scans are employed. I'm thinking I don't have something set correctly in my postgresql.conf file, but I'm not sure what. This table has approximately 300million rows. Version: SELECT version(); version -- PostgreSQL 8.4.2 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42), 64-bit We have 4 quad-core processors and 32GB of RAM. The below query uses the members_sorted_idx_001 index in oracle, but in postgres, the optimizer chooses a sequential scan. explain analyze create table tmp_srcmem_emws1 as select emailaddress, websiteid from members where emailok = 1 and emailbounced = 0; QUERY PLAN -- Seq Scan on members (cost=0.00..14137154.64 rows=238177981 width=29) (actual time=0.052..685834.785 rows=236660930 loops=1) Filter: ((emailok = 1::numeric) AND (emailbounced = 0::numeric)) Total runtime: 850306.220 ms (3 rows) show shared_buffers ; shared_buffers 7680MB (1 row) show effective_cache_size ; effective_cache_size -- 22GB (1 row) show work_mem ; work_mem -- 768MB (1 row) show enable_seqscan ; enable_seqscan on (1 row) Below are the data definitions for the table/indexes in question: \d members Table "members" Column|Type | Modifiers -+-+--- memberid| numeric | not null firstname | character varying(50) | lastname| character varying(50) | emailaddress| character varying(50) | password| character varying(50) | address1| character varying(50) | address2| character varying(50) | city| character varying(50) | statecode | character varying(50) | zipcode | character varying(50) | birthdate | date| emailok | numeric(2,0)| gender | character varying(1)| addeddate | timestamp without time zone | emailbounced| numeric(2,0)| changedate | timestamp without time zone | optoutsource| character varying(100) | websiteid | numeric | promotionid | numeric | sourceid| numeric | siteid | character varying(64) | srcwebsiteid| numeric | homephone | character varying(20) | homeareacode| character varying(10) | campaignid | numeric | srcmemberid | numeric | optoutdate | date| regcomplete | numeric(1,0)| regcompletesourceid | numeric | ipaddress | character varying(25) | pageid | numeric | streetaddressstatus | numeric(1,0)| middlename | character varying(50) | optinprechecked | numeric(1,0)| optinposition | numeric | homephonestatus | numeric | addeddate_id| numeric | changedate_id | numeric | rpmindex| numeric | optmode | numeric(1,0)| countryid | numeric | confirmoptin| numeric(2,0)| bouncedate | date| memberageid | numeric | sourceid2 | numeric | remoteuserid| character varying(50) | goal| numeric(1,0)| flowdepth | numeric | pagetype| numeric | savepassword| character varying(50) | customerprofileid | numeric | Indexes: "email_website_unq" UNIQUE, btree (emailaddress, websiteid), tablespace "members_idx" "member_addeddateid_idx" btree (addeddate_id), tablespace "members_idx" "member_changedateid_idx"
Re: [PERFORM] Slow count(*) again...
On Thu, Oct 14, 2010 at 12:22 AM, mark wrote: > Could this be an interesting test use of https://www.fossexperts.com/ ? > > 'Community' driven proposal - multiple people / orgs agree to pay various > portions? Maybe with multiple funders a reasonable target fund amount could > be reached. > > Just throwing around ideas here. This is a bit off-topic, but as of now, they're only accepting proposals for projects to be performed by CommandPrompt itself. So that doesn't help me much (note the sig). But in theory it's a good idea. Of course, when and if they open it up, then what? If more than one developer or company is interested in a project, who determines who gets to do the work and get paid for it? If that determination is made by CommandPrompt itself, or if it's just a free-for-all to see who can get their name on the patch that ends up being committed, it's going to be hard to get other people/companies to take it very seriously. Another problem is that even when they do open it up, they apparently intend to charge 7.5 - 15% of the contract value as a finder's fee. That's a lot of money. For a $100 project it's totally reasonable, but for a $10,000 project it's far more expensive than the value of the service they're providing can justify. (Let's not even talk about a $100,000 project.) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] oracle to psql migration - slow query in postgres
On Thu, Oct 14, 2010 at 12:43 PM, Tony Capobianco wrote: > We have 4 quad-core processors and 32GB of RAM. The below query uses > the members_sorted_idx_001 index in oracle, but in postgres, the > optimizer chooses a sequential scan. > > explain analyze create table tmp_srcmem_emws1 > as > select emailaddress, websiteid > from members > where emailok = 1 > and emailbounced = 0; Maybe a couple indexes to try: create index members_emailok_emailbounced_idx on members (emailok,emailbounced); or a functional index (will likely be smaller, depending on the contents of your table): create index members_emailok_emailbounced_idx on members (emailok,emailbounced) where emailok = 1 and emailbounced = 0; -- if you use that combination of 1 and 0 regularly -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] oracle to psql migration - slow query in postgres
On 10/14/10 21:43, Tony Capobianco wrote: We have 4 quad-core processors and 32GB of RAM. The below query uses the members_sorted_idx_001 index in oracle, but in postgres, the optimizer chooses a sequential scan. explain analyze create table tmp_srcmem_emws1 as select emailaddress, websiteid from members where emailok = 1 and emailbounced = 0; QUERY PLAN -- Seq Scan on members (cost=0.00..14137154.64 rows=238177981 width=29) (actual time=0.052..685834.785 rows=236660930 loops=1) Filter: ((emailok = 1::numeric) AND (emailbounced = 0::numeric)) Total runtime: 850306.220 ms (3 rows) Indexes: "email_website_unq" UNIQUE, btree (emailaddress, websiteid), tablespace "members_idx" "member_addeddateid_idx" btree (addeddate_id), tablespace "members_idx" "member_changedateid_idx" btree (changedate_id), tablespace "members_idx" "members_fdate_idx" btree (to_char_year_month(addeddate)), tablespace "esave_idx" "members_memberid_idx" btree (memberid), tablespace "members_idx" "members_mid_emailok_idx" btree (memberid, emailaddress, zipcode, firstname, emailok), tablespace "members_idx" "members_sorted_idx_001" btree (websiteid, emailok, emailbounced, addeddate, memberid, zipcode, statecode, emailaddress), tablespace "members_idx" "members_src_idx" btree (websiteid, emailbounced, sourceid), tablespace "members_idx" "members_wid_idx" btree (websiteid), tablespace "members_idx" PostgreSQL doesn't fetch data directly from indexes, so there is no way for it to reasonably use an index declared like: "members_sorted_idx_001" btree (websiteid, emailok, emailbounced, addeddate, memberid, zipcode, statecode, emailaddress) You need a direct index on the fields you are using in your query, i.e. an index on (emailok, emailbounced). OTOH, those columns look boolean-like. It depends on what your data set is, but if the majority of records contain (emailok=1 and emailbounced=0) an index may not help you much. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] oracle to psql migration - slow query in postgres
2010/10/14 Tony Capobianco : > We are in the process of testing migration of our oracle data warehouse > over to postgres. A potential showstopper are full table scans on our > members table. We can't function on postgres effectively unless index > scans are employed. I'm thinking I don't have something set correctly > in my postgresql.conf file, but I'm not sure what. > > This table has approximately 300million rows. and your query grab rows=236 660 930 of them. An index might be useless in this situation. > > Version: > SELECT version(); > > version > -- > PostgreSQL 8.4.2 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) > 4.1.2 20071124 (Red Hat 4.1.2-42), 64-bit > > We have 4 quad-core processors and 32GB of RAM. The below query uses > the members_sorted_idx_001 index in oracle, but in postgres, the > optimizer chooses a sequential scan. > > explain analyze create table tmp_srcmem_emws1 > as > select emailaddress, websiteid > from members > where emailok = 1 > and emailbounced = 0; > QUERY > PLAN > -- > Seq Scan on members (cost=0.00..14137154.64 rows=238177981 width=29) > (actual time=0.052..685834.785 rows=236660930 loops=1) > Filter: ((emailok = 1::numeric) AND (emailbounced = 0::numeric)) > Total runtime: 850306.220 ms > (3 rows) > > show shared_buffers ; > shared_buffers > > 7680MB > (1 row) > > show effective_cache_size ; > effective_cache_size > -- > 22GB > (1 row) > > show work_mem ; > work_mem > -- > 768MB > (1 row) > > show enable_seqscan ; > enable_seqscan > > on > (1 row) > > Below are the data definitions for the table/indexes in question: > > \d members > Table "members" > Column | Type | Modifiers > -+-+--- > memberid | numeric | not null > firstname | character varying(50) | > lastname | character varying(50) | > emailaddress | character varying(50) | > password | character varying(50) | > address1 | character varying(50) | > address2 | character varying(50) | > city | character varying(50) | > statecode | character varying(50) | > zipcode | character varying(50) | > birthdate | date | > emailok | numeric(2,0) | > gender | character varying(1) | > addeddate | timestamp without time zone | > emailbounced | numeric(2,0) | > changedate | timestamp without time zone | > optoutsource | character varying(100) | > websiteid | numeric | > promotionid | numeric | > sourceid | numeric | > siteid | character varying(64) | > srcwebsiteid | numeric | > homephone | character varying(20) | > homeareacode | character varying(10) | > campaignid | numeric | > srcmemberid | numeric | > optoutdate | date | > regcomplete | numeric(1,0) | > regcompletesourceid | numeric | > ipaddress | character varying(25) | > pageid | numeric | > streetaddressstatus | numeric(1,0) | > middlename | character varying(50) | > optinprechecked | numeric(1,0) | > optinposition | numeric | > homephonestatus | numeric | > addeddate_id | numeric | > changedate_id | numeric | > rpmindex | numeric | > optmode | numeric(1,0) | > countryid | numeric | > confirmoptin | numeric(2,0) | > bouncedate | date | > memberageid | numeric | > sourceid2 | numeric | > remoteuserid | character varying(50) | > goal | numeric(1,0) | > flowdepth | numeric | > pagetype | numeric | > savepassword | character varying(50) | > customerprofileid | numeric | > Indexes: > "email_website_unq"
Re: [PERFORM] oracle to psql migration - slow query in postgres
emailok | numeric(2,0)| Note that NUMERIC is meant for - really large numbers with lots of digits - or controlled precision and rounding (ie, order total isn't 99. $) Accordingly, NUMERIC is a lot slower in all operations, and uses a lot more space, than all the other numeric types. I see many columns in your table that are declared as NUMERIC but should be BOOLs, or SMALLINTs, or INTs, or BIGINTs. Perhaps Oracle handles these differently, I dunno. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] oracle to psql migration - slow query in postgres
Just my take on this. The first thing I'd do is think real hard about whether you really really want 'numeric' instead of boolean, smallint, or integer. The second thing is that none of your indices (which specify a whole bunch of fields, by the way) have only just emailok, emailbounced, or only the pair of them. Without knowing the needs of your app, I would reconsider your index choices and go with fewer columns per index. For this particular query I would think either two indexes (depending on the cardinality of the data, one for each of emailok, emailbounced) or one index (containing both emailok, emailbounced) would make quite a bit of difference. Consider creating the indexes using a WITH clause, for example: CREATE INDEX members_just_an_example_idx ON members (emailok, emailbounced) WHERE emailok = 1 AND emailbounced = 0; Obviously that index is only useful in situations where both fields are specified with those values. Furthermore, if the result is such that a very high percentage of the table has those conditions a sequential scan is going to be cheaper, anyway. -- Jon -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] oracle to psql migration - slow query in postgres
On 10/14/2010 4:10 PM, Jon Nelson wrote: The first thing I'd do is think real hard about whether you really really want 'numeric' instead of boolean, smallint, or integer. The second thing is that none of your indices (which specify a whole bunch of fields, by the way) have only just emailok, emailbounced, or only the pair of them. Without knowing the needs of your app, I would reconsider your index choices and go with fewer columns per index. Also, make sure that the statistics is good, that histograms are large enough and that Geico (the genetic query optimizer) will really work hard to save you 15% or more on the query execution time. You can also make sure that any index existing index is used, by disabling the sequential scan and then activating and de-activating indexes with the dummy expressions, just as it was done with Oracle's rule based optimizer. I agree that a good data model is even more crucial for Postgres than is the case with Oracle. Oracle, because of its rich assortment of tweaking & hacking tools and parameters, can be made to perform, even if the model is designed by someone who didn't apply the rules of good design. Postgres is much more susceptible to bad models and it is much harder to work around a badly designed model in Postgres than in Oracle. What people do not understand is that every application in the world will become badly designed after years of maintenance, adding columns, creating additional indexes, views, tables and triggers and than deploying various tools to design applications. As noted by Murphy, things develop from bad to worse. Keep Postgres models simple and separated, because it's much easier to keep clearly defined models simple and effective than to keep models with 700 tables and 350 views, frequently with conflicting names, different columns named the same and same columns named differently. And monitor, monitor, monitor. Use strace, ltrace, pgstatspack, auto_explain, pgfouine, pgadmin, top, sar, iostat and all tools you can get hold of. Without the event interface, it's frequently a guessing game. It is, however, possible to manage things. If working with partitioning, be very aware that PostgreSQL optimizer has certain problems with partitions, especially with group functions. If you want speed, everything must be prefixed with partitioning column: indexes, expressions, joins. There is no explicit star schema and creating hash indexes will not buy you much, as a matter of fact, Postgres community is extremely suspicious of the hash indexes and I don't see them widely used. Having said that, I was able to solve the problems with my speed and partitioning. -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow count(*) again...
On 2010-10-14 21:56, Robert Haas wrote: On Thu, Oct 14, 2010 at 12:22 AM, mark wrote: Could this be an interesting test use of https://www.fossexperts.com/ ? 'Community' driven proposal - multiple people / orgs agree to pay various portions? Maybe with multiple funders a reasonable target fund amount could be reached. Just throwing around ideas here. This is a bit off-topic, but as of now, they're only accepting proposals for projects to be performed by CommandPrompt itself. So that doesn't help me much (note the sig). But in theory it's a good idea. Of course, when and if they open it up, then what? If more than one developer or company is interested in a project, who determines who gets to do the work and get paid for it? If that determination is made by CommandPrompt itself, or if it's just a free-for-all to see who can get their name on the patch that ends up being committed, it's going to be hard to get other people/companies to take it very seriously. Couldnt you open up a dialog about it? Another problem is that even when they do open it up, they apparently intend to charge 7.5 - 15% of the contract value as a finder's fee. That's a lot of money. For a $100 project it's totally reasonable, but for a $10,000 project it's far more expensive than the value of the service they're providing can justify. (Let's not even talk about a $100,000 project.) Hi Robert. I can definately see your arguments, but you failed to describe a "better" way? Many of us rely heavily on PostgreSQL and would like to get "this feature", but sponsoring it all alone does not seem like a viable option (just a guess), taken into consideration we dont even have an estimate about how big it is, but I saw the estimate of 15K USD of the "ALTER column position" description.. and the visibillity map is most likely in the "same ballpark" (from my perspective). So in order to get something like a visibillity map (insert your favorite big feature here), you have the option: * Sponsor it all by yourself. (where its most likely going to be too big, or if it is the center of your applictions, then you definitely turn to a RDBMS that has supported it for longer times, if you can). * Wait for someone else to sponsor it all by them selves. (that happens occationally, but for particular features is it hard to see when and what, and the actual sponsor would still have the dilemma in the first point). * Hack it yourselves (many of us dont have time neither skills to do it, and my employer actually wants me to focus on the stuff that brings most direct value for my time, which is a category hacking PG does not fall into when the business is about something totally else). * A kind of microsponsoring like above? * Your proposal in here? To me.. the 4'th bullet point looks like the most viable so far.. To be honest, if it is EDB, Redpill, Command Prompt, 2nd Quadrant or whoever end up doing the job is, seen from this perspective not important, just it ends in the hands of someone "capable" of doing it. ... allthougth Heikki has done some work on this task allready. Preferrably I would like to get it coordinated by the PG project itself. But I can see that it is really hard to do that kind of stuff. And you would still face the challenge about who should end up doing the thing. Jesper .. dropped Joshua Drake on CC, he might have given all of this some seconds of thought allready. -- Jesper -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] odd postgresql performance (excessive lseek)
postgres 8.4.4 on openSUSE 11.3 (2.6.36rc7, x86_64). I was watching a fairly large query run and observed that the disk light went out. I checked 'top' and postgres was using 100% CPU so I strace'd the running process. This is what I saw: lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(87, 0, SEEK_END) = 585531392 lseek(94, 270680064, SEEK_SET) = 270680064 read(94, ""..., 8192) = 8192 and I observed that pattern quite a bit. I know lseek is cheap, but a superfluous systemcall is a superfluous systemcall, and over a short period amounted to 37% (according to strace) of the time spent in the system. What's with the excess calls to lseek? The query plan was a nested loop anti-join (on purpose). -- Jon -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
