Re: [PERFORM] Full text search with ORDER BY performance issue

On Mon, Jul 20, 2009 at 9:35 PM, Krade wrote: > But I think I might just do: > select * from a where comment_tsv @@ plainto_tsquery('query') and timestamp >> cast(floor(extract(epoch from CURRENT_TIMESTAMP) - 864000) as integer) > order by timestamp desc limit 24 offset 0; > > And if I get less th

Re: [PERFORM] Full text search with ORDER BY performance issue

On 7/21/2009 2:13, Devin Ben-Hur wrote: Have you tried make the full-text condition in a subselect with "offset 0" to stop the plan reordering? eg: select * from ( select * from a where comment_tsv @@ plainto_tsquery('love') offset 0 ) xx order by timestamp DESC limit 24 offset 0; See ht

Re: [PERFORM] Used computers?

Craig James wrote: Apologies for a slightly off-topic question ... a friend is overseeing the demise of a company and has several computers that they need to get rid of. She's an attorney and knows little about them except that they're IBM and cost >$50K originally. Where does one go to sell

Re: [PERFORM] Full text search with ORDER BY performance issue

Krade wrote: SELECT * FROM a WHERE comment_tsv @@ plainto_tsquery('love') ORDER BY timestamp DESC LIMIT 24 OFFSET 0; Have you tried make the full-text condition in a subselect with "offset 0" to stop the plan reordering? eg: select * from ( select * from a where comment_tsv @@ plainto_tsq

Re: [PERFORM] Full text search with ORDER BY performance issue

Hello, On 7/20/2009 22:42, Kevin Grittner wrote: Have you considered keeping rows "narrow" until you've identified your 24 rows? Something like: SELECT * FROM a WHERE id in ( SELECT id FROM a WHERE comment_tsv @@ plainto_tsquery('love') ORDER BY timestamp DESC

Re: [PERFORM] Calling conventions

"Kevin Grittner" writes: > Oh, well, if you load all the data into Java's heap and are accessing > it through HashMap or similar, I guess a factor of 100 is about right. > I see the big difference as the fact that the Java implementation is > dealing with everything already set up in RAM, versus n

Re: [PERFORM] Full text search with ORDER BY performance issue

Krade wrote: > SELECT * FROM a WHERE comment_tsv @@ plainto_tsquery('love') > ORDER BY timestamp DESC LIMIT 24 OFFSET 0; Have you considered keeping rows "narrow" until you've identified your 24 rows? Something like: SELECT * FROM a WHERE id in ( SELECT id FROM a WHERE co

Re: [PERFORM] Fastest char datatype

On Sun, Jul 19, 2009 at 9:46 PM, Robert James wrote: > I'm storing a lot of words in a database.  What's the fastest format for > finding them? I'm going to be doing a lot of WHERE w LIKE 'marsh%' and WHERE > w IN ('m', 'ma').  All characters are lowercase a-z, no punctuation, no > other alphabets.

Re: [PERFORM] Calling conventions

Matthew Wakeling wrote: > On Fri, 17 Jul 2009, Kevin Grittner wrote: >> but a factor of 100? > The Java code runs all in RAM, versus Postgres running all from OS > cache or Postgres shared buffer (bit hard to tell which of those > two it is - there is no hard drive activity anyway). The Jav

Re: [PERFORM] Full text search with ORDER BY performance issue

Hello, thanks for your replies. On 7/20/2009 13:12, Oleg Bartunov wrote: Hmm, everything is already written in explain :) In the first query 253635 rows should be readed from disk and sorted, while in the second query only 24 (random) rows readed from disk, so there is 4 magnitudes difference

Re: [PERFORM] XMLPARSE() evaluated multiple times?

Tim Landscheidt writes: > It seems that XMLPARSE() is called for every row without > PostgreSQL realizing that it is IMMUTABLE. Indeed, the system doesn't consider it immutable. None of the examples you show would benefit if it did, though. I believe there are GUC-parameter dependencies that pr

Re: [PERFORM] Used computers?

Perhaps these guys know: http://www.recurrent.com/ -kg On Jul 20, 2009, at 7:29 AM, Craig James wrote: Apologies for a slightly off-topic question ... a friend is overseeing the demise of a company and has several computers that they need to get rid of. She's an attorney and knows little

[PERFORM] XMLPARSE() evaluated multiple times?

Hi, I have been playing around with PostgreSQL's XML support lately (cf. news:m3ljmocolf@passepartout.tim-landscheidt.de>) and stumbled upon some performance issues related to XMLPARSE(). In my "application", the XML document is supp- lied as a string constant via a DBI ? parameter, for testin

Re: [PERFORM] Used computers?

Here in the UK, we have "Waste electrical and electronic equipment" (WEEE) companies that'll safely destroy or sell them on for a cut of the profits. --- On Mon, 20/7/09, Craig James wrote: > From: Craig James > Subject: [PERFORM] Used computers? > To: pgsql-performance@postgresql.org > Date:

Re: [PERFORM] Used computers?

On Mon, Jul 20, 2009 at 8:29 AM, Craig James wrote: > Apologies for a slightly off-topic question ... a friend is overseeing the > demise of a company and has several computers that they need to get rid of. >  She's an attorney and knows little about them except that they're IBM and > cost >$50K or

[PERFORM] Used computers?

Apologies for a slightly off-topic question ... a friend is overseeing the demise of a company and has several computers that they need to get rid of. She's an attorney and knows little about them except that they're IBM and cost >$50K originally. Where does one go to sell equipment like this

Re: [PERFORM] Can Postgres use an INDEX over an OR?

20 липня 2009 р. 11:02 Chris написав: > Віталій Тимчишин wrote: > >> >> >> 2009/7/20 Robert James > srobertja...@gmail.com>> >> >> >>Hi. I notice that when I do a WHERE x, Postgres uses an index, and >>when I do WHERE y, it does so as well, but when I do WHERE x OR y, >>it doesn't. Wh

Re: [PERFORM] Can Postgres use an INDEX over an OR?

Query is: select * from dict where word in (select substr('moon', 0, generate_series(3,length('moon' -- this is my X above OR word like 'moon%' -- this is my Y above dict is indexed on word 2009/7/20 Chris > 2009/7/20 Robert James srobertja...@gmail.com>> > > >Hi. I notice that when I

Re: [PERFORM] Fastest char datatype

Is there a way to use a more compact encoding? I only need 4 bits per char - that would certainly help caching. (I have indexes tuned very well, already). On Mon, Jul 20, 2009 at 2:02 AM, Peter Eisentraut wrote: > On Monday 20 July 2009 04:46:53 Robert James wrote: > > I'm storing a lot of word

Re: [PERFORM] Full text search with ORDER BY performance issue

On Sun, Jul 19, 2009 at 12:07 AM, Krade wrote: > archive=> explain analyze select * from a where  comment_tsv @@ > plainto_tsquery('love') order by timestamp desc limit 24 offset 0; What happens if you make it: select * from ( select * from a where comment_tsv @@plainto_tsquery('love')

Re: [PERFORM] Full text search with ORDER BY performance issue

Krade, On Sat, 18 Jul 2009, Krade wrote: Here's a couple of queries: archive=> explain analyze select * from a where comment_tsv @@ plainto_tsquery('love') order by timestamp desc limit 24 offset 0; QUERY PLAN -- Limit (cost=453248.73..453248.79 rows=24 width=281) (actual time=18

Re: [PERFORM] Calling conventions

On Fri, 17 Jul 2009, Kevin Grittner wrote: I've seen the code in Java outperform the same code in optimized C, because the "just in time" compiler can generate native code optimized for the actual code paths being taken rather than a compile-time guess at that, but a factor of 100? Something els

[PERFORM] Performance of quer or procedure going down when we are taking the backup

Dear all, Performance of query or procedure going down when we are taking the backup of that schema(it is obvious), But how to increase the performance. Regards, Ram. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscripti

Re: [PERFORM] Trigger on column

In response to ramasubramanian : > Dear all, >Can we create a trigger on particular column of a table? No, but you can compare OLD.column and NEW.column and return from the function if NEW.column = OLD.column. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639

[PERFORM] Trigger on column

Dear all, Can we create a trigger on particular column of a table? Regards, Ram -- 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] Can Postgres use an INDEX over an OR?

Віталій Тимчишин wrote: 2009/7/20 Robert James > Hi. I notice that when I do a WHERE x, Postgres uses an index, and when I do WHERE y, it does so as well, but when I do WHERE x OR y, it doesn't. Why is this so? It's not clever enough. Of cours