[GENERAL] Benchmarking partitioning triggers and rules

I wrote a quick benchmarking script to test various partitioning strategies. The code is here. https://gist.github.com/timuckun/954ab6bdce36fa14bc1c I was astonished at the variability of the timings between the different variations. The test data contained 270K records. I did a simple insert i

Re: [GENERAL] Benchmarking partitioning triggers and rules

Hey I hate to bump my post but I would really appreciate some input on this benchmark. I am very alarmed that adding a very simple partitioning trigger slows the insert speed by an order of magnitude. Is there any way to speed this up? On Mon, Mar 9, 2015 at 11:44 AM, Tim Uckun wrote: >

Re: [GENERAL] Benchmarking partitioning triggers and rules

:42 AM, Tomas Vondra wrote: > On 11.3.2015 21:43, Tim Uckun wrote: > > Hey I hate to bump my post but I would really appreciate some input > > on this benchmark. I am very alarmed that adding a very simple > > partitioning trigger slows the insert speed by an order of magnitu

[GENERAL] Name spacing functions and stored procedures

What do you guys do to namespace your functions so that they are not jumbled in with the system functions and also somewhat hierarchically organized. Obviously it's possible to create schemas for different namespaces but that can lead to a lot of schemas which hold no data. The other way is to s

Re: [GENERAL] Name spacing functions and stored procedures

I guess I see schemas as ways to group data not functions. It would be very nice to be able to group your code into proper modules though. It's something I really miss. >>

Re: [GENERAL] Benchmarking partitioning triggers and rules

I understand that there is overhead involved in parsing the strings and such. The amount of overhead was surprising to me but that's another matter. What I am really puzzled about is the difference between the statements EXECUTE 'INSERT INTO ' || quote_ident(partition_name) || ' SELECT ($1).*

Re: [GENERAL] Benchmarking partitioning triggers and rules

So is there a third and even faster way of doing this? On Wed, Apr 8, 2015 at 4:08 PM, Adrian Klaver wrote: > On 04/07/2015 07:49 PM, Tim Uckun wrote: > >> I understand that there is overhead involved in parsing the strings and >> such. The amount of overhead was surprisin

Re: [GENERAL] Help with slow table update

On Sat, Apr 18, 2015 at 10:24 AM, Pawel Veselov wrote: > I found some dangling prepared transactions How do you find and remove these?

[GENERAL] IDE for function/stored proc development.

Does anybody use an IDE for doing heavy duty stored proc development? PGadmin is decent but I am looking for something better. I have tried jetbrains with the db browser plugin and on the surface it seems like a good choice but it's really buggy when working with procs. I also tried datagrip by j

Re: [GENERAL] IDE for function/stored proc development.

postgres. On Sat, Sep 3, 2016 at 11:03 PM, Pavel Stehule wrote: > Hi > > 2016-09-03 11:36 GMT+02:00 Tim Uckun : > >> Does anybody use an IDE for doing heavy duty stored proc development? >> PGadmin is decent but I am looking for something better. >> >> I have

[GENERAL] Is there a way to fix this ugliness

I am trying to get the child elements of a one to many table to be rolled up into a json field in the parent table. The query I am running is select ob.id ,case when array_position(array_agg(im.image_type), null) = 1 then '[]' else json_agg(row_to_json(im.*)) end as images from

Re: [GENERAL] Is there a way to fix this ugliness

I could not make coalesce in the top level select to work even though people on the internet say it should work. I'll do the sub select that seems like it would work fine. On Sat, Sep 10, 2016 at 3:22 AM, Karl Czajkowski wrote: > On Sep 10, Tim Uckun modulated: > > I am trying to

[GENERAL] Performance PLV8 vs PLPGSQL

I have seen various links on the internet which indicate that PLV8 is significantly faster than PL-PGSQL sometimes an order of magnitude faster. Is this uniformly true or is it just in certain circumstances? Is there any benefit to choosing PL-PGSQL? Is there work going on to make PL-PGSQL more

Re: [GENERAL] Performance PLV8 vs PLPGSQL

AM, Mike Sofen wrote: > *From:* Tim Uckun > I have seen various links on the internet which indicate that PLV8 is > significantly faster than PL-PGSQL sometimes an order of magnitude faster. > > > > Is there any benefit to choosing PL-PGSQL? > > ---

Re: [GENERAL] Performance PLV8 vs PLPGSQL

I think it's awesome that postgres allows you to code in different languages like this. It really is a unique development environment and one that is overlooked as a development platform. It would be nice if more languages were delivered in the default package especially lua, V8 and mruby. On T

Re: [GENERAL] Performance PLV8 vs PLPGSQL

Mostly generating SQL statements to execute. Like for example deciding which partition to insert into. On Thu, Dec 29, 2016 at 10:00 PM, Pavel Stehule wrote: > > > 2016-12-29 9:23 GMT+01:00 Tim Uckun : > >> I am not doubting the efficacy of stored procs, just wondering wh

Re: [GENERAL] Performance PLV8 vs PLPGSQL

gonovo < m...@webthatworks.it> wrote: > On 12/29/2016 10:35 AM, Pavel Stehule wrote: > > 2016-12-29 10:03 GMT+01:00 Tim Uckun > <mailto:timuc...@gmail.com>>: >> >> I think it's awesome that postgres allows you to code in different >> langua

Re: [GENERAL] Performance PLV8 vs PLPGSQL

ng and writing faster and > faster ACID all the while. > > On Dec 29, 2016, at 5:32 PM, Tim Uckun wrote: > > Honestly I don't even like JS. Having said that I am not too crazy about > PL-PGSQL either. I am willing to put up with either given that they are > supported widely

Re: [GENERAL] Performance PLV8 vs PLPGSQL

here is my hat tip to the Postgres team for an awesome job they are > doing! > > On Dec 29, 2016, at 7:19 PM, Tim Uckun wrote: > > I am not saying the postgres core people should work on an IDE, just that > an IDE like thing would be nice. > > On Fri, Dec 30, 2016 at 12:51 PM

Re: [GENERAL] Performance PLV8 vs PLPGSQL

UI available. The devs made it an > absolute joy to use, and I never need to use the command line anymore. > > Hope this helps! > > On Dec 29, 2016, at 10:43 PM, Tim Uckun wrote: > > I have datagrip and it's OK but it doesn't really do everything I want. > > I do

[GENERAL] Importing directly from BCP files

Does anybody have any documentation on what a BCP file from SQL server looks like? I have a directory full of BCP files and I would like to import them into PG. Has anybody done anything like this before? Thanks

Re: [GENERAL] Importing directly from BCP files

> > > That appears to depend on the -f option > > > I have the format files so that's good. > > Have you tried opening a file in a text editor to see what they look like? > > Yes. It looks like a binary file with lots of non printable ch

Re: [GENERAL] Importing directly from BCP files

> > > I moved a database from MS Sql Server 2000 to Postgresql a few years > ago via BCP files. I used a Python script to do some fixup on the > BCP files to make them importable as CSV files into Postgresql. I > don't know if quirks I ran into are still an issue with newer versions > of Sql Serv

Re: [GENERAL] Importing directly from BCP files

I don't see any documentation anywhere about the BCP format and as I said googling for "BCP format" gives a zillion links about the format files. Every project on github just calls out the BCP command but I am on linux and that doesn't help me at all. Bummer. This is going to be a huge pain to tr

Re: [GENERAL] Importing directly from BCP files

> > > On openSuSE 13.2 > > sudo zypper install freetds-tools > > Ubuntu 14.04 > > sudo apt-get install freetds-bin > > aklaver@killi:~> freebcp -h > usage: freebcp [[database_name.]owner.]table_name {in | out} datafile > [-m maxerrors] [-f formatfile] [-e errfile] > [-F firstrow] [

Re: [GENERAL] Importing directly from BCP files

> > the binary form of the BCP file output is undocumented. So if all the BCP > files you have are the binary(native) version you are up this creek without > a paddle. > > Ugh. Yes it looks like that's the creek I am on. Thanks Microsoft! > So, moving to another creek. It depends on the amount

Re: [GENERAL] Importing directly from BCP files

I am going to ask them that but there are tons of legacy files which might need to be dealt with again in the future so I was hoping to use them directly.

Re: [GENERAL] using a postgres table as a multi-writer multi-updater queue

I'll add my two cents. I set up something similar a while ago. Here are my suggestions for what they are worth. You don't ever want to delete from such a table so you need to set up something which allows you to truncate the tables when you no longer need them. One way to accomplish this is

[GENERAL] Selecting top N percent of records.

Is there a way to select the top 10% of the values from a column? For example the top 10% best selling items where number of sales is a column. Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/

Re: [GENERAL] Selecting top N percent of records.

On Mon, Oct 18, 2010 at 12:53 PM, Darren Duncan wrote: > Tim Uckun wrote: >> >> Is there a way to select the top 10% of the values from a column? >> >> For example the top 10% best selling items where number of sales is a >> column. > > The top 10% would be

Re: [GENERAL] Selecting top N percent of records.

> That is a bit problematic because it necessitates knowing the number > of rows total, and slow counting is an idiosyncrasy of postgres. > > http://wiki.postgresql.org/wiki/Slow_Counting > > To get the top 10%: > > SELECT * FROM table LIMIT(SELECT (COUNT(*) * 0.1)::integer FROM table) I think I

Re: [GENERAL] Selecting top N percent of records.

> > OK, so you want a median-style "sort them in descending order and count down > until you've selected the first 10% of rows" approach? In other words, > values in the 90th percentile of the distribution? > > Try this. Given table "x" with single integer column "y", obtain rows of x > in the 90th

[GENERAL] Updates, deletes and inserts are very slow. What can I do make them bearable?

I have a very simple update query. update cu set screenshot_file_name = tu.screenshot_file_name, screenshot_content_type = tu.screenshot_content_type, screenshot_file_size = tu.screenshot_file_size, screenshot_status = tu.screenshot_status from cu inner join tu on tu.cu_id = cu.id

Re: [GENERAL] Updates, deletes and inserts are very slow. What can I do make them bearable?

On Thu, Oct 21, 2010 at 3:37 PM, Gary Chambers wrote: >> Where is the FAST button for postgres updates? What parameter do I >> have to set in order to update 6000 records in under an hour? > > Which version of Postgres? 8.4  >Have you investigated more than just two > performance tuning paramet

Re: [GENERAL] Updates, deletes and inserts are very slow. What can I do make them bearable?

> > 1) Increase checkpoint_segments (>64, increases beyond that can be helpful > but they eventually level out) Changed it back to 64 from 256 > 2) Increase shared_buffers (~25% of RAM is normal) Changed it to one gig (25% of my RAM) obviously this involved changing the shmmax and shmall setting

Re: [GENERAL] Updates, deletes and inserts are very slow. What can I do make them bearable?

To follow up... I did a full vacuum analyze on both tables and re-ran the query. Same story. I ended the query after eight minutes. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Updates, deletes and inserts are very slow. What can I do make them bearable?

On Thu, Oct 21, 2010 at 5:05 PM, Tom Lane wrote: > Tim Uckun writes: >> I have a very simple update query. > >> update cu >> set screenshot_file_name = tu.screenshot_file_name, >>     screenshot_content_type  = tu.screenshot_content_type, >>     screenshot

Re: [GENERAL] Updates, deletes and inserts are very slow. What can I do make them bearable?

> > No, it isn't.  This is a three-way join between consolidated_urls, cu, > and tu --- the fact that cu is the same underlying table as cu is an alias for consolidated_urls. tu is an alias for trending_urls. There are only two tables in the query. > consolidated_urls doesn't change that.  And t

Re: [GENERAL] Updates, deletes and inserts are very slow. What can I do make them bearable?

One more follow up. Did a vacuum full on both tables and a re-index on both tables. Changed the wal_buffers to 16MB (increased the kernel param as a result) as per http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server I also turned off fsync but still no joy. -- Sent via pgsql-general

Re: [GENERAL] Updates, deletes and inserts are very slow. What can I do make them bearable?

On Fri, Oct 22, 2010 at 4:33 AM, Brian Hirt wrote: >> >> There are only two tables in the query. >> > > Tim, > > No, your query is written incorrectly.  I don't understand why you come on to > this list all hostile and confrontational.  Regardless, people still try to > help you and then you sti

Re: [GENERAL] Updates, deletes and inserts are very slow. What can I do make them bearable?

>>> True. His only real snark was in reponse to the "let me google that > for you" link. OTOH, he's arguing with Tom Lane about whether his SQL > is well formed. There's arguing on the internet is stupid, then > there's arguing with Tom Lane about SQL is stupid. I wasn't arguing with Tom Lane a

Re: [GENERAL] Updates, deletes and inserts are very slow. What can I do make them bearable?

> Agreed.  But when Tom pointed out the problem in your query you were > quite sure you couldn't be wrong.  When I asked you to run explain to > see what kind of row estimate you got, I got no answer.  This was a > query problem not a hardware tuning problem. > > The best step for getting a good an

Re: [GENERAL] Updates, deletes and inserts are very slow. What can I do make them bearable?

> It's unfortunate that the first response you got was that message from Gary > Chambers, which was a bit unprofessional and started the whole thread off in > a bad direction for you.  As what I've seen of Gary's posts suggests he is > trying to be helpful but has a quirky sense of humor, I think t

[GENERAL] Postgres connection errors

Hello. I have lots of ruby daemons running connected to postgres. Some of them start getting connection errors after about a day or two of running. The odd thing is that they don't all get the same error. Some get this error: PGError: lost synchronization with server: got message type "T" Others

Re: [GENERAL] Postgres connection errors

> > Most of the cases we've seen like that have been because multiple > threads in the client application were trying to use the same PGconn > connection object concurrently.  There's no cross-thread synchronization > built into libpq, so you have to provide the interlocks yourself if > there's any

[GENERAL] Adding an "and is not null" on an indexed field slows the query down immensely.

I have this query it runs reasonably quickly. SELECT "consolidated_urls".* FROM "consolidated_urls" INNER JOIN "topical_urls" ON "consolidated_urls".id = "topical_urls".consolidated_url_id WHERE (("topical_urls".domain_id = 157) AND ((topical_urls.hidden = 'f'))) ORDER BY index_delta DESC LIM

Re: [GENERAL] Adding an "and is not null" on an indexed field slows the query down immensely.

2011/1/15 pasman pasmański : > Try : > order by index_delta+1 desc > I have attached the explain analyze for that below why does this return instantly? Limit (cost=29910.05..29910.07 rows=10 width=1880) (actual time=42.563..42.563 rows=0 loops=1) -> Sort (cost=29910.05..29916.65 rows=264

Re: [GENERAL] Adding an "and is not null" on an indexed field slows the query down immensely.

2011/1/16 pasman pasmański : > I think this is a planner's bug. Can you send these explains to pgsql-bugs ? > Sure. BTW I thought I would change the query a little by putting a AND index_value .>100 instead of index_delta and it didn't help at all. I thought maybe using another index would help

[GENERAL] Fast data, slow data

I have a use case in which the most recent data experiences a lot of transactions (inserts and updates) and then the churn kind of calms down. Eventually the data is relatively static and will only be updated in special and sporatic events. I was thinking about keeping the high churn data in a di

Re: [GENERAL] Fast data, slow data

cause vacuum problems. On Fri, Jun 27, 2014 at 2:49 AM, Shaun Thomas wrote: > On 06/26/2014 04:29 AM, Tim Uckun wrote: > > I don't think partitioning is a good idea in this case because the >> partitions will be for small time periods (5 to 15 minutes). >> > >

Re: [GENERAL] Fast data, slow data

ation at this point. I don't want to say that the data is not important but if I drop one or two sensor readings it's not going to be the end of the world. On Fri, Jun 27, 2014 at 3:45 AM, Steve Crawford < scrawf...@pinpointresearch.com> wrote: > On 06/26/2014 02:29 AM, Tim Uc

[GENERAL] Managing Key Value tags on rows

What is the most efficient way to model key value tags on records. The keys and values will be variable and changing over time. The values may be of various types (numbers, dates, strings etc). There will be filters and group by selects based on tag names and maybe even tag values. Postgres giv

[GENERAL] Hierarchical rollups.

I am looking to model a hierarchical structure where the parent nodes on each level will contain calculations based on immediate children's values. For example the parent row may have an average of all the child rows (the calculations will be more complicated than that of course). So every time a

Re: [GENERAL] Managing Key Value tags on rows

re wrote: > On Wed, Nov 12, 2014 at 7:17 PM, Tim Uckun wrote: > > What is the most efficient way to model key value tags on records. The > keys > > and values will be variable and changing over time. The values may be of > > various types (numbers, dates, strings etc). Th

[GENERAL] Getting truncated queries from pg_stat_statements

I am trying to get some slow query information and the results from my query are truncated at 2047 characters. Some of my queries are very long so they get truncated in the select. Is there a way around this? Here is my query. WITH query_stats AS ( SELECT query::text,

Re: [GENERAL] Getting truncated queries from pg_stat_statements

Ok thanks. I am still on 9.3 so I'll adjust that setting. On Tue, Jan 20, 2015 at 12:15 PM, Peter Geoghegan < peter.geoghega...@gmail.com> wrote: > On Mon, Jan 19, 2015 at 2:37 PM, Tim Uckun wrote: > > I am trying to get some slow query information and the results fro

Re: [GENERAL] Getting truncated queries from pg_stat_statements

Mon, Jan 19, 2015 at 6:41 PM, Tim Uckun wrote: > >> Ok thanks. I am still on 9.3 so I'll adjust that setting. >> >> On Tue, Jan 20, 2015 at 12:15 PM, Peter Geoghegan < >> peter.geoghega...@gmail.com> wrote: >> >>> On Mon, Jan 19, 2015 at 2:37 PM

[GENERAL] Postgres seems to use indexes in the wrong order

Take a look at this explain http://explain.depesz.com/s/TTRN The final number of records is very small but PG is starting out with a massive number of records and then filtering most of them out. I don't want to really force pg to always use the same index because in some cases this strategy wou

Re: [GENERAL] Postgres seems to use indexes in the wrong order

The effective_cache_size is one gig. The others are not set so therefore the default. On Sun, Jan 25, 2015 at 6:08 AM, Sameer Kumar wrote: > > On Fri, Jan 23, 2015 at 3:04 PM, Tim Uckun wrote: > >> Take a look at this explain >> >> http://explain.depesz.com/s/TTRN

Re: [GENERAL] Postgres seems to use indexes in the wrong order

Kumar wrote: > > On Fri, Jan 23, 2015 at 3:04 PM, Tim Uckun wrote: > >> Take a look at this explain >> >> http://explain.depesz.com/s/TTRN >> > > ​Adding some info on the query and table structure (and indexes) would be > helpful here.​ > > >> >

Re: [GENERAL] Postgres seems to use indexes in the wrong order

Thanks for the tips. I'll make some adjustments On Tue, Jan 27, 2015 at 8:38 PM, Sameer Kumar wrote: > > On Tue, Jan 27, 2015 at 6:59 AM, Tim Uckun wrote: > >> The query seems to first use the timestamp column which results in a huge >> number of records and then fil

[GENERAL] Partioning with overlapping and non overlapping constraints

I have two partitioning questions I am hoping somebody can help me with. I have a fairly busy metric(ish) table. It gets a few million records per day, the data is transactional for a while but then settles down and is used for analytical purposes later. When a metric is reported both the UTC tim

Re: [GENERAL] Partioning with overlapping and non overlapping constraints

= 1 > > or just > EXTRACT(YEAR FROM utc_time) = 2015 > > > On Thu, Feb 5, 2015 at 10:12 PM, David G Johnston < > david.g.johns...@gmail.com> wrote: > >> Tim Uckun wrote >> > 1. Should I be worried about having possibly hundreds of thousands of >>

Re: [GENERAL] Partioning with overlapping and non overlapping constraints

>Don't you have duplicate information within your UTC, location and local_time data ? Maybe you can just attach a timezone to each location... Yes there is duplicate information but dealing with time zones are a PITA and the easiest way to solve the myriad of problems I have is to store the local

Re: [GENERAL] Partioning with overlapping and non overlapping constraints

This approach wouldn't work I think. The data keeps growing in the "hot" table. On Tue, Feb 10, 2015 at 6:01 AM, Melvin Davidson wrote: > Well, without knowing too much about your application, it certainly sounds > like using the metricts_MMDD is the way to go. As for modifying the > constr

[GENERAL] What's a reasonable maximum number for table partitions?

The documentation says having too many partitions will end up being unproductive as it will cause the optimizer to examine all the tables for query planning. So I am wondering what's a reasonable upper limit? If I was to partition a table by day I would have 365 tables per year. Is that too many?

Re: [GENERAL] What's a reasonable maximum number for table partitions?

Feb 12, 2015 at 7:44 PM, Tim Uckun wrote: > >> Does anybody have experience with huge number of partitions if so where >> did you start running into trouble? >> > > I use an arbitrary 100-way split for a lot of tracking info. Just modulo > 100 on the ID column. I've n

Re: [GENERAL] What's a reasonable maximum number for table partitions?

, 2015 at 11:15 AM, Bill Moran wrote: > On Sat, 14 Feb 2015 11:14:10 +1300 > Tim Uckun wrote: > > > If I used modulo arithmetic how would the query optimizer know which > table > > to include and exclude? For example say I did modulo 100 based on the > field > >

[GENERAL] Triggers and scalability in high transaction tables.

I want to write a trigger which runs semi-complicated code after each insert. I have done some reading and from what I can gather this could cause problems because after insert triggers "don't spill to the disk" and can cause queue problems. Many people suggest LISTEN NOTIFY but that's not going

Re: [GENERAL] Triggers and scalability in high transaction tables.

external process. Thanks. On Fri, Feb 27, 2015 at 11:12 AM, Jerry Sievers wrote: > Tim Uckun writes: > > > I want to write a trigger which runs semi-complicated code after each > insert. I have done some reading and from what I can gather this could > cause problems becaus

Re: [GENERAL] Running update in chunks?

> Can you try a couple of things just to check timings. Probably worth EXPLAIN > ANALYSE. > > SELECT count(*) FROM cars.imports i JOIN cars.models md ON i.model_id = > md.id; Takes about 300 ms > > CREATE TEMP TABLE tt AS SELECT i.* FROM cars.imports i JOIN cars.models md > ON i.model_id = md.id

Re: [GENERAL] Running update in chunks?

Just to close this up and give some guidance to future googlers... There are no foreign keys. The table is empty when I start. I copy the data into it from a text file. Removing indexes didn't help much (made a very slight difference). running a query CREATE TEMP TABLE tt AS SELECT using a

Re: [GENERAL] Running update in chunks?

> > Nope. If you have any suggestions I am all ears. For the purposes of this discussion we can narrow down the problem this update statement. Update imports set make_id = null. There are 98K records in the table. There is no index on the make_id field. Standard untouched postgresql.conf from th

Re: [GENERAL] Running update in chunks?

> I'd be curious to see results of the same "update" on a standard HDD > vs the SSD, and maybe on a more typical database deployment hardware > vs a macbook air. > I haven't tried it on any other machine yet. CREATE TEMP TABLE tt as SELECT ... takes eight seconds so presumably the disk is not t

Re: [GENERAL] Running update in chunks?

> First off, what does it say for rows affected? (Hint, if you really > are using a default configuration and it doesn't say 0 rows > affected, please show us the actual query used.) update imports set make_id = null Query returned successfully: 98834 rows affected, 49673 ms execution time. vac

Re: [GENERAL] Running update in chunks?

Oh I forgot SELECT version(); "PostgreSQL 9.2.2 on x86_64-apple-darwin12.2.1, compiled by Apple clang version 4.1 (tags/Apple/clang-421.11.65) (based on LLVM 3.1svn), 64-bit" SELECT name, current_setting(name), source FROM pg_settings WHERE source NOT IN ('default', 'override'); "appl

Re: [GENERAL] Running update in chunks?

I already posted the schema earlier. It's a handful of integer fields with one hstore field. On Tue, Jan 22, 2013 at 1:23 PM, Steve Crawford wrote: > select * from pg_stat_user_tables where relname='yourtable'; Messy output "relid","schemaname","relname","seq_scan","seq_tup_read","idx_scan",

Re: [GENERAL] Running update in chunks?

Sorry I haven't been responsive for a little while. I ran your script but creating a new schema instead of my existing schema. My timings were similar to yours (more or less) except fo the vacuums which took roughly 147891 ms. On Tue, Jan 22, 2013 at 3:21 PM, Kevin Grittner wrote: > Kevin Grit

Re: [GENERAL] Running update in chunks?

> > What if you do: > alter table cars.imports set (fillfactor=50); > Before the vacuum full, and then try the update again? This makes a dramatic difference when combined with a vacuum. UPDATE 98834 Time: 3408.210 ms Ten times faster! -- Sent via pgsql-general mailing list (pgsql-general@po

Re: [GENERAL] Running update in chunks?

autoanalyze_count | 25 On Wed, Jan 23, 2013 at 7:50 AM, Steve Crawford wrote: > On 01/21/2013 05:02 PM, Tim Uckun wrote: >> >> I already posted the schema earlier. It's a handful of integer fields >> with one hstore field. > > > Oh well. I can't find it bu

Re: [GENERAL] Running update in chunks?

> I agree that seems like the most likely cause. Each update to the > row holding the hstore column requires adding new index entries for > all the hstore elements, and autovacuum will need to clean up the > old ones in the background. The best solution would be to either > normalize the data inste

Re: [GENERAL] Running update in chunks?

> > That suggests (to me, at least) that it is related to index updating. Again, > your GIN index seems primary candidate. > > Try running iostat (I think that's available on a Mac) with/without the > fillfactor and with/without the GIN index while you do the updates. It's > possible your SSD is ju

Re: [GENERAL] Running update in chunks?

> > That would be one solution, but I think a better one would be to not > store "make_id" in "imports" in the first place, but instead to always > fetch it by joining "imports" to "models" at query time. > My problem here is that the incoming data is quite messy so the join conditions become weir

[GENERAL] Dropping default privileges.

I am unable to drop a user. drop role tim; ERROR: role "tim" cannot be dropped because some objects depend on it DETAIL: owner of default privileges on new relations belonging to role tim in schema strongmail ALTER DEFAULT PRIVILEGES IN SCHEMA strongmail REVOKE INSERT, SELECT, UPDATE, DELET

Re: [GENERAL] Dropping default privileges.

>> drop role tim; >> ERROR: role "tim" cannot be dropped because some objects depend on it >> DETAIL: owner of default privileges on new relations belonging to >> role tim in schema strongmail > > DROP OWNED BY ought to get rid of that. Just to be clear. I don't want to drop the schema. Will D

Re: [GENERAL] Dropping default privileges.

Thanks. Worked. On Wed, Jan 30, 2013 at 9:12 PM, Albe Laurenz wrote: > Tim Uckun wrote: >>>> drop role tim; >>>> ERROR: role "tim" cannot be dropped because some objects depend on it >>>> DETAIL: owner of default privileges on new relation

[GENERAL] Upsert Functionality using CTEs

I am using a query like this to try and normalize a table. WITH nd as (select * from sales order by id limit 100), people_update as (update people p set first_name = nd.first_name from nd where p.email = nd.email returning nd.id), insert into people (first_name, email, created_at, updated_at)

Re: [GENERAL] Upsert Functionality using CTEs

> The best strategy is fixing your data-model so that you have a unique key. As > you found out already, e-mail addresses aren't very suitable as unique keys > for people. For this particular case I'd suggest adding a surrogate key. > > Alternatively, you might try using (first_name, email) as yo

Re: [GENERAL] Upsert Functionality using CTEs

> > > I read it that he has multiple sales from the same person? In which case > pretending that the two sales were from different people isn't the correct > result at all. Actually it turns out that both cases exist. > > I may be missing the point of the query, but wasn't it to add an entry for

[GENERAL] Why is this a cross join?

I have a seemingly simple query that returns way too many records. Here is the count query select count(*) from ( select crm.* from sap_crm_sales crm inner join model_configurations mc on left(crm.customer_class, 6) = left(mc.sap_code,6) ) as s The crm table has about 44K records, th

Re: [GENERAL] Why is this a cross join?

> Apparently the first 6 characters of those fields are quite common, which > gives you a result for every possible combination of the same 6-character > value. M. That seems kind of weird. Is there any way to NOT have this be a cross join? For example if I extracted the first six characte

Re: [GENERAL] Why is this a cross join?

> > In some way, every join is a cross join, with the results filtered according > to the specificity of the join conditions. In this case: > > inner join model_configurations mc on left(crm.customer_class, 6) = > left(mc.sap_code,6) > > "customer_class" sounds like a fairly generic sort of field

Re: [GENERAL] Why is this a cross join?

> for every row in one of the tables? If so, how do you want to > determine which of the matching rows in the other table to choose, > and which to ignore? In this case it's a simple lookup. Any of the matching rows will do but I just want one. -- Sent via pgsql-general mailing list (pgsql-ge

[GENERAL] Using varchar primary keys.

Consider the following scenario. I have a typical tagging structure. There is a table called tags, there is a table called taggings. The taggings table acts as a many to many join table between the taggers and the tags. The tags table has two fields id and tag. id is a serial data type. The tagg

Re: [GENERAL] Using varchar primary keys.

> > > Natural Keys have a purpose but when do they exist in the database? > > In my case it's the tags. Table tags has only two columns id and tag. Each one has a unique index. I have many other similar lookup tables. For example things like order_status, item_type etc. > What about the Natural

Re: [GENERAL] Using varchar primary keys.

> > how about using an enum instead of this table? > > That's an interesting idea. Are enums mutable?

Re: [GENERAL] Using varchar primary keys.

On Tue, Apr 2, 2013 at 8:35 AM, jesusthefrog wrote: > On the topic of 'natural' versus 'synthetic' primary keys, I am generally > in the camp that an extra ID field won't cost you too much, and while one > may not need it for a simple table (i.e. id, name) one might add any number > of columns la

[GENERAL] Shortcut evaluation in OR or IN

Say I have a select like this. SELECT * FROM table where field = X OR field = Y limit 1 And I have two records one that matches X and one that matches Y will I always get X because the evaluation will stop after the first clause in the OR matches? What about for IN (X, Y) how about if I am doin

Re: [GENERAL] Shortcut evaluation in OR or IN

Thanks for the explanation. On Mon, May 6, 2013 at 8:43 PM, Jasen Betts wrote: > On 2013-05-06, Tim Uckun wrote: > > --047d7b2e4ea07402b004dc034a3b > > Content-Type: text/plain; charset=UTF-8 > > > > Say I have a select like this. > > > > SELECT *

[GENERAL] "Reverse" inheritance?

I am trying to make postgres tables work like an object hierarchy. As an example I have done this. drop table if exists os.linux cascade; create table os.linux ( script_name character varying(255) NOT NULL, script_body text, CONSTRAINT os_linux_pkey PRIMARY KEY (script_name) ); drop table if exis

  1   2   3   >