Re: [PERFORM] Performance Solaris vs Linux
Fredrik Bertilsson wrote: Hi, we are using Postgres on both Solaris servers and Linux servers, and Postgres are much slower on Solaris servers. We have tested with different versions of Solaris and Postgres, but the fact remains: Postgres seems to be much faster on Linux server. Does anybody else has the same experience? Best regards, Fredrik B I had some performance problems on Solaris a while ago which let to this interesting thread: http://archives.postgresql.org/pgsql-performance/2006-04/thrd4.php#00035 executive summary: - write cache might be (unexpectedly) off by default on sun gear - set explicitly "wal_sync_method = fsync" - some other settings (see thread) Bye, Chris. ---(end of broadcast)--- TIP 1: 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] Fwd: Table Partitioning
10 aug 2007 kl. 22:58 skrev Nurlan Mukhanov: Hello All. I have a table with ca. 100.000.000 records. The main idea is make Partitioning for this table (1000 or 1 tables). Let's take for example. CREATE TABLE test ( id integer, data date not null default now() ) WITHOUT OIDS; CREATE TABLE test00 ( CHECK ( id%100 = 0 ) ) INHERITS (test); CREATE TABLE test01 ( CHECK ( id%100 = 1 ) ) INHERITS (test); ... CREATE TABLE test09 ( CHECK ( id%100 = 9 ) ) INHERITS (test); -- RULES CREATE OR REPLACE RULE test00 AS ON INSERT TO test WHERE (NEW.id%100) = 0 DO INSTEAD INSERT INTO test00 (id) VALUES (NEW.id); CREATE OR REPLACE RULE test01 AS ON INSERT TO test WHERE (NEW.id%100) = 1 DO INSTEAD INSERT INTO test01 (id) VALUES (NEW.id); ... CREATE OR REPLACE RULE test09 AS ON INSERT TO test WHERE (NEW.id%100) = 9 DO INSTEAD INSERT INTO test09 (id) VALUES (NEW.id); So the main algorithm is to take last digits of ID and put to special table. Yes, it is work correct. But when I make a selection query database ask all table instead of one I'm not sure this will make any difference but are you using SET constraint_exclusion = on; ? . "Aggregate (cost=134.17..134.18 rows=1 width=0)" " -> Append (cost=4.33..133.94 rows=90 width=0)" "-> Bitmap Heap Scan on test01 (cost=4.33..14.88 rows=10 width=0)" " Recheck Cond: (id = 1)" " -> Bitmap Index Scan on test01_id (cost=0.00..4.33 rows=10 width=0)" "Index Cond: (id = 1)" "-> Bitmap Heap Scan on test02 (cost=4.33..14.88 rows=10 width=0)" " Recheck Cond: (id = 1)" " -> Bitmap Index Scan on test02_id (cost=0.00..4.33 rows=10 width=0)" "Index Cond: (id = 1)" "-> Bitmap Heap Scan on test03 (cost=4.33..14.88 rows=10 width=0)" " Recheck Cond: (id = 1)" " -> Bitmap Index Scan on test03_id (cost=0.00..4.33 rows=10 width=0)" "Index Cond: (id = 1)" "-> Bitmap Heap Scan on test04 (cost=4.33..14.88 rows=10 width=0)" " Recheck Cond: (id = 1)" " -> Bitmap Index Scan on test04_id (cost=0.00..4.33 rows=10 width=0)" "Index Cond: (id = 1)" "-> Bitmap Heap Scan on test05 (cost=4.33..14.88 rows=10 width=0)" " Recheck Cond: (id = 1)" " -> Bitmap Index Scan on test05_id (cost=0.00..4.33 rows=10 width=0)" "Index Cond: (id = 1)" "-> Bitmap Heap Scan on test06 (cost=4.33..14.88 rows=10 width=0)" " Recheck Cond: (id = 1)" " -> Bitmap Index Scan on test06_id (cost=0.00..4.33 rows=10 width=0)" "Index Cond: (id = 1)" "-> Bitmap Heap Scan on test07 (cost=4.33..14.88 rows=10 width=0)" " Recheck Cond: (id = 1)" " -> Bitmap Index Scan on test07_id (cost=0.00..4.33 rows=10 width=0)" "Index Cond: (id = 1)" "-> Bitmap Heap Scan on test08 (cost=4.33..14.88 rows=10 width=0)" " Recheck Cond: (id = 1)" " -> Bitmap Index Scan on test08_id (cost=0.00..4.33 rows=10 width=0)" "Index Cond: (id = 1)" "-> Bitmap Heap Scan on test09 (cost=4.33..14.88 rows=10 width=0)" " Recheck Cond: (id = 1)" " -> Bitmap Index Scan on test09_id (cost=0.00..4.33 rows=10 width=0)" "Index Cond: (id = 1)" If change CHECK to CREATE TABLE test00 ( CHECK ( id = 0 ) ) INHERITS (test); CREATE TABLE test01 ( CHECK ( id = 1 ) ) INHERITS (test); ... etc - everything work correct, only one table is asked for data. Are your first check algorithm causing overlaps? Cheers, henrik But how to implement my idea if ID is always increment and have range from 1 to BIGINT? How it is possible or is there any variants to store different IDs in separated tables when CHECK condition will be used during SELECT or DELETE queries? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Help optimize view
>>> On Fri, Aug 10, 2007 at 11:57 AM, in message
<[EMAIL PROTECTED]>, "Relyea,
Mike" <[EMAIL PROTECTED]> wrote:
> I'm have the following view as part of a larger, aggregate query that is
> running slower than I'd like.
> . . .
> HAVING "PrintSamples"."MachineID" = 4741 OR "PrintSamples"."MachineID" =
> 4745 AND "AnalysisModules"."AnalysisModuleName" = 'NMF' AND
> "ParameterNames"."ParameterName" = 'NMF' AND "tblColors"."ColorID" <> 3
> AND "PrintSamples"."TestPatternName" LIKE 'IQAF-TP8%';
First off, let's make sure we're optimizing the query you really want to run.
AND binds tighter than OR, so as you have it written, it is the same as:
HAVING "PrintSamples"."MachineID" = 4741
OR ( "PrintSamples"."MachineID" = 4745
AND "AnalysisModules"."AnalysisModuleName" = 'NMF'
AND "ParameterNames"."ParameterName" = 'NMF'
AND "tblColors"."ColorID" <> 3
AND "PrintSamples"."TestPatternName" LIKE 'IQAF-TP8%';
)
I fear you may really want it evaluate to:
HAVING ("PrintSamples"."MachineID" = 4741 OR "PrintSamples"."MachineID" =
4745)
AND "AnalysisModules"."AnalysisModuleName" = 'NMF'
AND "ParameterNames"."ParameterName" = 'NMF'
AND "tblColors"."ColorID" <> 3
AND "PrintSamples"."TestPatternName" LIKE 'IQAF-TP8%';
-Kevin
---(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] Help optimize view
>>> On Fri, Aug 10, 2007 at 11:57 AM, in message <[EMAIL PROTECTED]>, "Relyea, Mike" <[EMAIL PROTECTED]> wrote: > HAVING "PrintSamples"."MachineID" = 4741 OR "PrintSamples"."MachineID" = > 4745 AND . . . On top of the issue in my prior email, I don't see any test for 4745 in the EXPLAIN ANALYZE output, which makes me think it doesn't go with the posted query. -Kevin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
