Re: [PERFORM] bad COPY performance with NOTIFY in a trigger

[Harald Fuchs]

Tom Lane  writes:

> No surprise, see AsyncExistsPendingNotify.  You would have a lot of other
> performance issues with sending hundreds of thousands of distinct notify
> events from one transaction anyway, so I can't get terribly excited about
> this.

@Filip: you probably want a per-statement trigger rather than a per-row
trigger: insert all rows with COPY, then send one notification.

You have to mark the new rows somehow yourself; unfortunately PostgreSQL
has no way to tell them in a statement trigger.



-- 
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] best db schema for time series data?

[Harald Fuchs]

In article 4ce2688b.2050...@tweakers.net,
Arjen van der Meijden acmmail...@tweakers.net writes:

 On 16-11-2010 11:50, Louis-David Mitterrand wrote:
 I have to collect lots of prices from web sites and keep track of their
 changes. What is the best option?
 
 1) one 'price' row per price change:
 
 create table price (
 id_price primary key,
 id_product integer references product,
 price integer
 );
 
 2) a single 'price' row containing all the changes:
 
 create table price (
 id_price primary key,
 id_product integer references product,
 price integer[] -- prices are 'pushed' on this array as they change
 );
 
 Which is bound to give the best performance, knowing I will often need
 to access the latest and next-to-latest prices?

 If you mostly need the last few prices, I'd definitaly go with the
 first aproach, its much cleaner. Besides, you can store a date/time
 per price, so you know when it changed. With the array-approach that's
 a bit harder to do.

I'd probably use a variant of this:

  CREATE TABLE prices (
pid int NOT NULL REFERENCES products,
validTil timestamp(0) NULL,
price int NOT NULL,
UNIQUE (pid, validTil)
  );

The current price of a product is always the row with validTil IS NULL.
The lookup should be pretty fast because it can use the index of the
UNIQUE constraint.


-- 
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] Strange workaround for slow query

[Harald Fuchs]

In article 
of6136ad9b.d40f3af5-onc12576e2.002d5763-c12576e2.002fb...@imtechrelay.nl,
sverha...@wps-nl.com writes:
 SELECT * FROM events_events LEFT OUTER JOIN events_event_types ON 
 eventType_id=
 events_event_types.id WHERE severity=70 AND (eventType_id IN (71)) ORDER BY
 datetime DESC LIMIT 50;
 Now I have at least two possibilities:
 - Implementing the dummy value as shown above in my source code to improve
 query performance (dirty but effective)
 - Further investigating what is going on, which at this point is something I
 need help with

First I'd change the query.  You build an OUTER JOIN and immediately
convert it to an INNER JOIN by referencing the severity column.

Then show us what EXPLAIN ANALYZE says to the problem.


-- 
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] 12 hour table vacuums

[Harald Fuchs]

In article [EMAIL PROTECTED],
Ron St-Pierre [EMAIL PROTECTED] writes:

 For what it's worth NUMERIC columns take more space than you might expect.
 Figure a minimum of 12 bytes your rows are at about 1.5k each even if the
 non-numeric columns aren't large themselves. What are the other columns?

 The NUMERIC columns hold currency related values, with values ranging
 from a few cents to the billions, as well as a few negative numbers.

What's the required precision? If it's just cents (or maybe tenths
thereof), you could use BIGINT to store the amount in this precision.
This would give you exact values with much less space.


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [PERFORM] Query tuning help

[Harald Fuchs]

In article [EMAIL PROTECTED],
Dan Harris [EMAIL PROTECTED] writes:

 On May 8, 2005, at 8:06 PM, Josh Berkus wrote:
 
 If I were to use tsearch2 for full-text indexing, would I need to
 create another table that merges all of my recordtext rows into a
 single 'text' field type?
 
 No.   Read the OpenFTS docs, they are fairly clear on how to set up
 a simple
 FTS index. (TSearch2 ~~ OpenFTS)
 
 If so, this is where I run into problems, as
 my logic also needs to match multiple words in their original order.

 I have been reading the Tsearch2 docs and either I don't understand
 something or I'm not communicating my situation clearly enough.  It
 seems that Tsearch2 has a concept of document.  And, in everything I
 am reading, they expect your document to be all contained in a
 single row.  Since my words can be spread across multiple rows, I
 don't see that Tsearch2 will combine all 'recordtext' row values with
 the same incidentid into a single vector.  Am I overlooking
 something in the docs?

AFAICS no, but you could create a separate table containing just the
distinct incidentids and the tsearch2 vectors of all recordtexts
matching that incidentid.  This table would get updated solely by
triggers on the original table and would provide a fast way to get all
incidentids for RED and CORVETTE.  The question is: would this reduce
the number of rows to check more than filtering on date?


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] COPY vs INSERT

[Harald Fuchs]

In article [EMAIL PROTECTED],
Dennis Bjorklund [EMAIL PROTECTED] writes:

 On Fri, 6 May 2005, Jim C. Nasby wrote:
 Has thought been given to supporting inserting multiple rows in a single
 insert? DB2 supported:
 
 INSERT INTO table VALUES(
 (1,2,3),
 (4,5,6),
 (7,8,9)
 );
 
 I'm not sure how standard that is or if other databases support it.

 The sql standard include this, except that you can not have the outer ().
 So it should be

 INSERT INTO table VALUES
 (1,2,3),
 (4,5,6),
 (7,8,9);

Since MySQL has benn supporting this idiom for ages, it can't be
standard ;-)


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [PERFORM] COPY Hacks (WAS: RE: Postgresql vs SQLserver for this application ?)

[Harald Fuchs]

In article [EMAIL PROTECTED],
Mischa [EMAIL PROTECTED] writes:

 This thread seems to be focusing in on COPY efficiency,
 I'd like to ask something I got no answer to, a few months ago.

 Using COPY ... FROM STDIN via the Perl DBI (DBD::Pg) interface,
 I accidentally strung together several \n-terminated input lines,
 and sent them to the server with a single putline.

 To my (happy) surprise, I ended up with exactly that number of rows
 in the target table.

 Is this a bug? Is this fundamental to the protocol?

 Since it hasn't been documented (but then, endcopy isn't documented),
 I've been shy of investing in perf testing such mass copy calls.
 But, if it DOES work, it should be reducing the number of network 
 roundtrips.

 So. Is it a feechur? Worth stress-testing? Could be VERY cool.

Using COPY from DBD::Pg _is_ documented - presumed you use DBD::Pg
version 1.41 released just today.


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PERFORM] How to read query plan

[Harald Fuchs]

In article [EMAIL PROTECTED],
=?ISO-8859-15?Q?Miroslav_=A6ulc?= [EMAIL PROTECTED] writes:

 Instead of a varchar(1) containing 'y' or 'n' you could use a
 BOOL or an  integer.

 Sure I could. The problem is our project still supports both MySQL and
 PostgreSQL. We used enum('Y','N') in MySQL so there would be a lot of
 changes in the code if we would change to the BOOL data type.

Since BOOL is exactly what you want to express and since MySQL also
supports BOOL (*), you should make that change anyway.

(*) MySQL recognizes BOOL as a column type and silently uses
TINYINT(1) instead.


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [PERFORM] Benchmark

[Harald Fuchs]

In article [EMAIL PROTECTED],
Greg Stark [EMAIL PROTECTED] writes:

 Christopher Browne [EMAIL PROTECTED] writes:
 After all, the cost of a computer system to run the transactions is
 likely to be comprised of some combination of software licenses and
 hardware costs.  Even if the software is free, the hardware isn't.

 And labour costs.

Except that working with PostgreSQL is fun, not labour :-)


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [PERFORM] integer[] indexing.

[Harald Fuchs]

In article [EMAIL PROTECTED],
=?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?= [EMAIL PROTECTED] writes:

   disclaimer : brainless proposition

 (SELECT * FROM table WHERE (icount(ids) = 1 AND ids[1] = 33)
 UNION ALL
 (SELECT * FROM table WHERE (icount(ids)  1 AND ids  '{33}'));

I guess my proposition is even more brainless :-)

If 95% of all records have only one value, how about putting the first
(and most often only) value into a separate column with a btree index
on it?  Something like that:

  CREATE TABLE tbl (
-- other columns
id1 INT NOT NULL,
idN INT[] NULL
  );

  CREATE INDEX tbl_id1_ix ON tbl (id1);

If id1 is selective enough, you probably don't need another index on idn.


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PERFORM] Caching of Queries

[Harald Fuchs]

In article [EMAIL PROTECTED],
Scott Kirkwood [EMAIL PROTECTED] writes:

 I couldn't find anything in the docs or in the mailing list on this,
 but it is something that Oracle appears to do as does MySQL.
 The idea, I believe, is to do a quick (hash) string lookup of the
 query and if it's exactly the same as another query that has been done
 recently to re-use the old parse tree.

That's not was MySQL is doing.  MySQL caches not the query plan, but
the result set for the (hashed) query string.  If the same query comes
again, it is not executed at all (unless one of the tables involved
have been changed meanwhile).


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

[Harald Fuchs]

In article [EMAIL PROTECTED],
Leeuw van der, Tim [EMAIL PROTECTED] writes:

 So what I can say is, that if you want fast INSERT performance from
 PostgreSQL then you'll probably have to do some trickery that you
 wouldn't have to do with a default MySQL installation.

I think the word INSERT is superfluous in the above sentence ;-)

Contrary to MySQL, you can't expect decent PostgreSQL performance on
decent hardware without some tuning.


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [PERFORM] Slow in morning hours

[Harald Fuchs]

In article [EMAIL PROTECTED],
[EMAIL PROTECTED] writes:

 Hi All,
 I am using Linux 7.2 and postgresql 7.2.

 Our Office hours are over at 6pm but we use to keep our server 
 running 24 hours a day.  On the second day morning, Our PGSQL 
 Server becomes very slow.

 After continuous usage of one hour, It gradually starts responding 
 faster ! This has become every day routine !

 do u have any idea related to this  Is there any other reason that I 
 need to check up?

 Please any any idea to get relief daily morning problem !!

I guess you're doing a VACUUM at night which invalidates the buffer
cache.  If that's what happens, it's easy to fix: run some dummy
queries after the VACUUM which cause the buffer cache to get filled.


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html