[PERFORM] Re: [PERFORM] Re: Query > 1000× slowdown after adding datetime comparison

2015-08-31 21:46 GMT+02:00 twoflower wrote: > I created a new boolean column and filled it for every row in DOCUMENT with > *(doc.date_last_updated >= date(now() - '171:00:00'::interval))*, reanalyzed > ... ... and you've put an index on that new boolean column (say

[PERFORM] Re: [PERFORM] Re: Query > 1000× slowdown after adding datetime comparison

So, if I'm understanding you correctly, we're talking solely about following clause in the query you gave initially: WHERE doc.date_last_updated >= date(now() - '171:00:00'::interval) which initially was WHERE documenttype = 4 and now is being replaced by a temporary (I'd say derived) column

Re: [PERFORM] Getting query plan alternatives from query planner?

Hi Craig and Shawn I fully agree with your argumentation. Who's the elephant in the room who is reluctant to introduce explicit hints? -S. 2014-04-14 17:35 GMT+02:00 Craig James cja...@emolecules.com: Shaun Thomas stho...@optionshouse.com wrote: these issues tend to get solved through

Re: [PERFORM] Getting query plan alternatives from query planner?

to introduce planner hinting (like in EnterpriseDB or Ora)? Regards, Stefan 2014-03-20 18:08 GMT+01:00 Tom Lane t...@sss.pgh.pa.us: Stefan Keller sfkel...@gmail.com writes: I'd like to know from the query planner which query plan alternatives have been generated and rejected. Is this possible

[PERFORM] Getting query plan alternatives from query planner?

Hi, I'd like to know from the query planner which query plan alternatives have been generated and rejected. Is this possible? --Stefan -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription:

Re: [PERFORM] FTS performance issue - planner problem identified (but only partially resolved)

://www.postgresql.org/message-id/ca+tgmozgqbeu2kn305hwds+axw7yp0yn9vzwbsbwa8unst+...@mail.gmail.com 2013/7/29 Kevin Grittner kgri...@ymail.com: Stefan Keller sfkel...@gmail.com wrote: Finally, setting random_page_cost to 1 helps also - but I don't like this setting neither. Well, you should learn to like

Re: [PERFORM] How to properly index hstore tags column to faster search for keys

Hi Yuri and Radu-Stefan I would'nt give too fast on PostgreSQL! When looking at your query plan I wonder if one could reformulate the query to compute the ST_DWithin first (assuming you have an index on the node geometries!) before it filters the tags. To investigate that you could formulate a

[PERFORM] Re: FTS performance issue - planner problem identified (but only partially resolved)

default values for enable_seqscan and set random_page_cost. Yours, S. 2013/7/19 Stefan Keller sfkel...@gmail.com: Hi At 2013/2/8 I wrote: I have problems with the performance of FTS in a query like this: SELECT * FROM FullTextSearch WHERE content_tsv_gin @@ plainto_tsquery('english', 'good

Re: [PERFORM] FTS performance issue - planner problem identified (but only partially resolved)

Hi Marc Thanks a lot for your hint! You mean doing a SET track_counts (true); for the whole session? That would be ok if it would be possible just for the gin index. It's obviously an issue of the planner estimation costs. The data I'm speaking about (movies) has a text attribute which has a

[PERFORM] FTS performance issue - planner problem identified (but only partially resolved)

Hi At 2013/2/8 I wrote: I have problems with the performance of FTS in a query like this: SELECT * FROM FullTextSearch WHERE content_tsv_gin @@ plainto_tsquery('english', 'good'); It's slow ( 30 sec.) for some GB (27886 html files, originally 73 MB zipped). The planner obviously always

[PERFORM]

Hi, I'm experiencing a very slow CTE query (see below). When I split the three aggregationns into separate views, its' decent fast. So I think it's due to the planner. Any ideas how to reformulate the query? These are the tables and views involved: * Table promotion with start/end date and a

[PERFORM] Slow CTE Query

Hi, I'm experiencing a very slow CTE query (see below). When I split the three aggregations into three separate views, its' decent fast. So I think it's due to the planner. Any ideas like reformulating the query? These are the tables and views involved: * Table promotion with start/end date

Re: [PERFORM] FTS performance issue probably due to wrong planner estimate of detoasting

Hi Jesper and Pavel Thx for your hints. I'm rather reluctant in tuning with unwanted side effects, We'll see. I have to setup my system and db again before I can try out your tricks. Yours, Stefan 2013/2/8 Jesper Krogh jes...@krogh.cc: On 08/02/13 01:52, Stefan Keller wrote: Hi, I have

[PERFORM] FTS performance issue probably due to wrong planner estimate of detoasting

Hi, I have problems with the performance of FTS in a query like this: SELECT * FROM FullTextSearch WHERE content_tsv_gin @@ plainto_tsquery('english', 'good'); It's slow ( 30 sec.) for some GB (27886 html files, originally 73 MB zipped). The planner obviously always chooses table scan:

Re: [PERFORM] Index over all partitions (aka global index)?

, Stefan Keller sfkel...@gmail.com wrote: Say, there is a table with 250 mio. rows split into 250 tables with 1 mio. rows each. And say the the index behavior is O(log n). Then a search for a key takes O(log(250*n)) or 8.4 time units. What PG (9.1) currently probably does is a iterative call

[PERFORM] Index over all partitions (aka global index)?

Hi, Given I have a large table implemented with partitions and need fast access to a (primary) key value in a scenario where every minute updates (inserts/updates/deletes) are coming in. Now since PG does not allow any index (nor constraint) on master table, I have a performance issue (and a

Re: [PERFORM] Inserts in 'big' table slowing down the database

longer than an hour, it delays the next update. Any ideas? Partitioning? Yours, S. 2012/9/3 Ivan Voras ivo...@freebsd.org: On 03/09/2012 13:03, Stefan Keller wrote: Hi, I'm having performance issues with a simple table containing 'Nodes' (points) from OpenStreetMap: CREATE TABLE nodes

[PERFORM] Inserts in 'big' table slowing down the database

Hi, I'm having performance issues with a simple table containing 'Nodes' (points) from OpenStreetMap: CREATE TABLE nodes ( id bigint PRIMARY KEY, user_name text NOT NULL, tstamp timestamp without time zone NOT NULL, geom GEOMETRY(POINT, 4326) ); CREATE INDEX

Re: [PERFORM] Slow query: Select all buildings that have 1 pharmacies and 1 schools within 1000m

Hi 2012/8/8 Jeff Janes jeff.ja...@gmail.com: On Tue, Aug 7, 2012 at 5:07 PM, Stefan Keller sfkel...@gmail.com wrote: Hi Craig Clever proposal! I slightly tried to adapt it to the hstore involved. Now I'm having a weird problem that PG says that relation 'p' does not exist. Why does PG

[PERFORM] Slow query: Select all buildings that have 1 pharmacies and 1 schools within 1000m

Hi I have an interesting query to be optimized related to this one [1]. The query definition is: Select all buildings that have more than 1 pharmacies and more than 1 schools within a radius of 1000m. The problem is that I think that this query is inherently O(n^2). In fact the solution I

Re: [PERFORM] Slow query: Select all buildings that have 1 pharmacies and 1 schools within 1000m

Your proposal lacks the requirement that it's the same building from where pharmacies and schools are reachable. But I think about. Yours, S. 2012/8/7 Tomas Vondra t...@fuzzy.cz: On 7 Srpen 2012, 14:01, Stefan Keller wrote: Hi I have an interesting query to be optimized related to this one

Re: [PERFORM] Slow query: Select all buildings that have 1 pharmacies and 1 schools within 1000m

p.value = 'school' AND ST_DWithin(b.way,p.way,1000) ) ERROR: relation p does not exist LINE 14: FROM p 2012/8/7 Craig James cja...@emolecules.com: On Tue, Aug 7, 2012 at 5:01 AM, Stefan Keller sfkel...@gmail.com wrote: Hi I have an interesting query to be optimized related to this one

Re: [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?

2012/3/1 Jeff Janes jeff.ja...@gmail.com: On Tue, Feb 28, 2012 at 3:46 PM, Stefan Keller sfkel...@gmail.com wrote: 2012/2/28 Claudio Freire klaussfre...@gmail.com: In the OP, you say There is enough main memory to hold all table contents.. I'm assuming, there you refer to your current system

Re: [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?

2012/2/29 Jeff Janes jeff.ja...@gmail.com: It's quite possible the vacuum full is thrashing your disk cache due to maintainance_work_mem. You can overcome this issue with the tar trick, which is more easily performed as: tar cf /dev/null $PG_DATA/base But on many implementations, that will

Re: [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?

2012/2/29 Stefan Keller sfkel...@gmail.com: 2012/2/29 Jeff Janes jeff.ja...@gmail.com: It's quite possible the vacuum full is thrashing your disk cache due to maintainance_work_mem. You can overcome this issue with the tar trick, which is more easily performed as: tar cf /dev/null $PG_DATA

[PERFORM] Re: 回复： [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?

Hi Wales 2012/2/27 Wales Wang wormw...@yahoo.com wrote: There are many approach for PostgreSQL in-memory. The quick and easy way is making slave pgsql run on persistent RAM filesystem, the slave is part of master/slave replication cluster. The fstab and script make RAM file system persistent

Re: [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?

2012/2/28 Claudio Freire klaussfre...@gmail.com: On Tue, Feb 28, 2012 at 5:48 PM, Stefan Keller sfkel...@gmail.com wrote: P.S. And yes, the database is aka 'read-only' and truncated and re-populated from scratch every night. fsync is off so I don't care about ACID. After the indexes on name

Re: [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?

Hi Jeff and Wales, 2012/2/26 Jeff Janes jeff.ja...@gmail.com wrote: The problem is that the initial queries are too slow - and there is no second chance. I do have to trash the buffer every night. There is enough main memory to hold all table contents. Just that table, or the entire

Re: [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?

2012/2/26 Andy Colson a...@squeakycode.net wrote: On 02/25/2012 06:16 PM, Stefan Keller wrote: 1. How can I warm up or re-populate shared buffers of Postgres? 2. Are there any hints on how to tell Postgres to read in all table contents into memory? Yours, Stefan How about after you load

Re: [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?

Hi, 2012/2/26 Cédric Villemain ced...@2ndquadrant.fr wrote: 1. How can I warm up or re-populate shared buffers of Postgres? There was a patch proposed for postgresql which purpose was to Which patch are you referring to? snapshot/Restore postgresql buffers, but it is still not sure how far

[PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?

Hi, 2011/10/24 Stephen Frost sfr...@snowman.net wrote Now, we've also been discussing ways to have PG automatically re-populate shared buffers and possibly OS cache based on what was in memory at the time of the last shut-down, but I'm not sure that would help your case either since you're

Re: [PERFORM] hstore query: Any better idea than adding more memory?

Hi Stephen Thanks for your answer and hints. 2011/10/24 Stephen Frost sfr...@snowman.net wrote: * Stefan Keller (sfkel...@gmail.com) wrote: Adding more memory (say to total of 32 GB) would only postpone the problem. Erm, seems like you're jumping to conclusions here... Sorry. I actually only

[PERFORM] hstore query: Any better idea than adding more memory?

Hi all I'd like to tune the following hstore-related query which selects all Zoos from table osm_point: SELECT osm_id, name, tags FROM osm_point WHERE tags @ hstore('tourism','zoo') ORDER BY name; ... given the following table and indexes definition: CREATE TABLE osm_point ( osm_id

Re: [PERFORM] Hash index use presently(?) discouraged since 2005: revive or bury it?

exploiting all advantages of a separate hash index, would'nt it? Stefan 2011/9/18 Merlin Moncure mmonc...@gmail.com: On Sat, Sep 17, 2011 at 4:48 PM, Jeff Janes jeff.ja...@gmail.com wrote: On Tue, Sep 13, 2011 at 5:04 PM, Peter Geoghegan pe...@2ndquadrant.com wrote: On 14 September 2011 00:04, Stefan

Re: [PERFORM] Hash index use presently(?) discouraged since 2005: revive or bury it?

this to encourage ourselves that enhancing hash indexes could be worthwhile. Stefan 2011/9/18 Kevin Grittner kevin.gritt...@wicourts.gov: Stefan Keller  wrote: It's hard for me to imagine that btree is superior for all the issues mentioned before. It would be great if you could show

[PERFORM] What about implementing a bitmap index? Any use cases?

Hi, I know there exist Bitmap Index Scan and Bitmap Heap Scan in Postgres. What about implementing a bitmap index for explicit use (CREATE INDEX ...)? Any use cases? Bitmap indexes work best on values with low cardinality (categorical data), would be efficient in space and ready for logic

[PERFORM] Index containing records instead of pointers to the data?

Hi, Sorry if this is an odd question: I assume that Postgres indexes don't store records but only pointers to the data. This means, that there is always an additional access needed (real table I/O). Would an index containing data records make sense? Stefan -- Sent via pgsql-performance mailing

[PERFORM] PostgreSQL-related topics of theses and seminary works sought (Was: Hash index use presently(?) discouraged...)

2011/9/17 Tomas Vondra t...@fuzzy.cz wrote: (...) We've been asked by a local university for PostgreSQL-related topics of theses and seminary works I'm also interested in such proposals or ideas! Here's some list of topics: * Adding WAL-support to hash indexes in PostgreSQL (see ex-topic) *

Re: [PERFORM] Hash index use presently(?) discouraged since 2005: revive or bury it?

2011/9/16 Tom Lane t...@sss.pgh.pa.us: I'm not entirely following this eagerness to junk that AM, anyway. We've put a lot of sweat into it over the years, in the hopes that it would eventually be good for something.  It's on the edge of being good for something now, and there's doubtless room

Re: [PERFORM] Hash index use presently(?) discouraged since 2005: revive or bury it?

a ticket? Stefan 2011/9/14 Tom Lane t...@sss.pgh.pa.us: Peter Geoghegan pe...@2ndquadrant.com writes: On 14 September 2011 00:04, Stefan Keller sfkel...@gmail.com wrote: Has this been verified on a recent release? I can't believe that hash performs so bad over all these points. Theory tells me

Re: [PERFORM] Hash index use presently(?) discouraged since 2005: revive or bury it?

much of work (in man days) do you estimate would this mean for someone who can program but has to learn PG internals first? Stefan 2011/9/14 Tom Lane t...@sss.pgh.pa.us: Peter Geoghegan pe...@2ndquadrant.com writes: On 14 September 2011 00:04, Stefan Keller sfkel...@gmail.com wrote: Has this been

Re: [PERFORM] Postgres for a data warehouse, 5-10 TB

Interesting debate. 2011/9/13 Marti Raudsepp ma...@juffo.org: Yes, every BEGIN/EXCEPTION block creates a subtransaction -- like a SAVEPOINT it can roll back to in case of an error. Are you sure? In theory I always understood that there are no subtransactions. In fact when looking at the docs

[PERFORM] Hash index use presently(?) discouraged since 2005: revive or bury it?

The doc at http://www.postgresql.org/docs/current/interactive/indexes-types.html says: Caution: Hash index operations are not presently WAL-logged, so hash indexes might need to be rebuilt with REINDEX after a database crash. They are also not replicated over streaming or file-based replication.

Fwd: [PERFORM] Summaries on SSD usage?

Shaun, 2011/9/2 Shaun Thomas stho...@peak6.com: Ironically, this is actually the topic of my presentation at Postgres Open. Do you think my problem would now be solved with NVRAM PCI card? Stefan -- Forwarded message -- From: Stefan Keller sfkel...@gmail.com Date: 2011/9/3

Re: [PERFORM] Summaries on SSD usage?

2011/9/3 Jesper Krogh jes...@krogh.cc: On 2011-09-03 00:04, Stefan Keller wrote: It's not that hard to figure out.. take some of your typical queries. say the one above..  Change the search-term to something you'd expect the user to enter in a minute, but hasn't been run. (could be museum

Re: [PERFORM] Summaries on SSD usage?

2011/9/2 Scott Marlowe scott.marl...@gmail.com: On Tue, Aug 30, 2011 at 11:23 AM, Stefan Keller sfkel...@gmail.com wrote: How big is your DB? What kind of reads are most common, random access or sequential? How big of a dataset do you pull out at once with a query. SSDs are usually not a big

Re: [PERFORM] Summaries on SSD usage?

You mean something like Unlogged Tables in PostgreSQL 9.1 (= in-memory database) or simply a large ramdisk? Yours, Stefan 2011/9/1 Jim Nasby j...@nasby.net: On Aug 30, 2011, at 12:23 PM, Stefan Keller wrote: I'm looking for summaries (or best practices) on SSD usage with PostgreSQL. My use

[PERFORM] Summaries on SSD usage?

Hi, I'm looking for summaries (or best practices) on SSD usage with PostgreSQL. My use case is mainly a read-only database. Are there any around? Yours, Stefan -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription:

Re: [PERFORM] hstore - Implementation and performance issues around its operators

/19 Robert Haas robertmh...@gmail.com: On Sun, Jun 19, 2011 at 2:59 PM, Stefan Keller sfkel...@gmail.com wrote: 1. Obviously the '@' has to be used in order to let use the GiST index. Why is the '-' operator not supported by GiST ('-' is actually mentioned in all examples of the doc.)? Because

[PERFORM] hstore - Implementation and performance issues around its operators

Hi, We did a benchmark comparing a Key-Value-Pairs stored as EAV db schema versus hstore. The results are promising in favor of hstore but there are some question which remain. 1. Obviously the '@' has to be used in order to let use the GiST index. Why is the '-' operator not supported by GiST

Re: FW: [PERFORM] KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation)

Hi all Thank you to all who answered: That worked: CREATE INDEX planet_osm_point_tags_amenity ON planet_osm_point ((tags-'amenity')) WHERE (tags-'amenity') IS NOT NULL; My problem is, that in fact I don't know which tag to index since I'm running a web admin application where users can enter

Re: FW: [PERFORM] KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation)

Salut Pierre You wrote Try to create a btree index on (bench_hstore-bench_id) WHERE (bench_hstore-bench_id) IS NOT NULL. What do you mean exactly? = CREATE INDEX myhstore_kps_gin_idx ON myhstore USING gin(kvps) WHERE ??? IS NOT NULL; My table's def is: CREATE TABLE myhstore ( id bigint

Re: [PERFORM] KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation)

from OpenStreetMap (http://wiki.openstreetmap.org/wiki/Database_schema ). Yours, Stefan 2011/5/17 Jim Nasby j...@nasby.net: On May 16, 2011, at 8:47 AM, Merlin Moncure wrote: On Sat, May 14, 2011 at 5:10 AM, Stefan Keller sfkel...@gmail.com wrote: Hi, I am conducting a benchmark to compare

[PERFORM] KVP table vs. hstore - hstore performance (Was: Postgres NoSQL emulation)

Hi, I am conducting a benchmark to compare KVP table vs. hstore and got bad hstore performance results when the no. of records is greater than about 500'000. CREATE TABLE kvp ( id SERIAL PRIMARY KEY, key text NOT NULL, value text ); -- with index on key CREATE TABLE myhstore ( id SERIAL PRIMARY

[PERFORM] How to configure a read-only database server?

I browsed the faq and looked at PostgreSQL performance books but I could not find the obvious: How to configure a read-only database server? I have a single-disk virtual Linux system and a read-only dataset which is exposed to internet and completely replaced from time to time. This is what I