Re: [PERFORM] Excessive context switching on SMP Xeons
Hmmm... I may be mistaken (I think last time I read about optimization params was in 7.3 docs), but doesn't RPC 1 mean that random read is faster than sequential read? In your case, do you really think reading randomly is 4x faster than reading sequentially? Doesn't seem to make sense, even with a zillion-disk array. Theoretically. Also not sure, but sort_mem and vacuum_mem seem to be too small to me. G. %--- cut here ---% \end - Original Message - From: Bill Montgomery [EMAIL PROTECTED] Sent: Wednesday, October 06, 2004 5:45 PM Some relevant parameters: shared_buffers = 16384 sort_mem = 2048 vacuum_mem = 16384 max_fsm_pages = 20 max_fsm_relations = 1 fsync = true wal_sync_method = fsync wal_buffers = 32 checkpoint_segments = 6 effective_cache_size = 262144 random_page_cost = 0.25 ---(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] *very* inefficient choice made by the planner (regarding
Dear Gurus, - Original Message - From: Stephan Szabo [EMAIL PROTECTED] Sent: Thursday, June 10, 2004 7:14 PM On Thu, 10 Jun 2004, Stephan Szabo wrote: On Thu, 10 Jun 2004, Jean-Luc Lachance wrote: I agree, but it should be a simple rewrite. No? It's NULLs inside the subselect that are the issue. select 1 in (select a from foo) select exists ( select 1 from foo where a=1) Just a dumb try :) SELECT (exists(select 1 from foo where a isnull) AND NULL) OR exists(select 1 from foo where a=1) AFAIK this returns * NULL if (NULL in foo.a) and (1 not in foo.a) * (1 in foo.a) otherwise. The weakness is the doubled exists clause. I'm sure it makes most cases at least doubtful... G. %--- cut here ---% \end ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Dual Xeon + HW RAID question
by default -- do you mean there is a way to tell Linux to favor the second real cpu over the HT one? how? G. --- cut here --- - Original Message - From: Bruce Momjian [EMAIL PROTECTED] Sent: Tuesday, July 22, 2003 6:26 PM Subject: Re: [PERFORM] Dual Xeon + HW RAID question Right, I simplified it. The big deal is whether the OS favors the second real CPU over one of the virtual CPU's on the same die --- by default, it doesn't. Ever if it did work perfectly, you are talking about going from 1 to 1.4 or 2 to 2.8, which doesn't seem like much. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Optimizer differences between 7.2 and 7.3
A bit OT: do regex ops (~, ~*) use index scan in non-C locales? Is it worth to convert LIKE to regex? G. --- cut here --- - Original Message - From: Richard Huxton [EMAIL PROTECTED] Sent: Monday, July 07, 2003 4:40 PM Check the locale the database was initdb'd to. You'll probably find 7.2.4 is in the C locale whereas 7.3.3 isn't. The like comparison can only use indexes in the C locale. I believe you might need to initdb again to fix this. -- Richard Huxton ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Dual Xeon + HW RAID question
Alexandre, I missed your orig. post, but AFAIK multiprocessing kernels will handle HT CPUs as 2 CPUs each. Thus, our dual Xeon 2.4 is recognized as 4 Xeon 2.4 CPUs. This way, I don't think HT would improve any single query (afaik no postgres process uses more than one cpu), but overall multi-query performance has to improve. - Original Message - From: Nikolaus Dilger [EMAIL PROTECTED] Sent: Saturday, July 12, 2003 8:25 PM Alexandre, Since you want the fastest speed I would do the 2 data disks in RAID 0 (striping) not RAID 1 (mirroring). If you would care about not loosing any transactions you would keep all 3 disks in RAID 5. Don't know the answer to the Hyperthreading question. Why don't you run a test to find out? Regards, Nikolaus On Thu, 10 Jul 2003 14:43:25 -0300 (BRT), alexandre arruda paes :: aldeia digital wrote: Hi, I have this machine with a 10 million records: * Dual Xeon 2.0 (HyperThreading enabled), 3 7200 SCSI , Adaptec 2110S, RAID 5 - 32k chunk size, 1 GB Ram DDR 266 ECC, RH 8.0 - 2.4.18 The database is mirrored with contrib/dbmirror in a P4 1 Gb Ram + IDE If a disk failure occurs, I can use the server in the mirror. I will format the main server in this weekend and I have seen in the list some people that recomends a Software RAID instead HW. I think too remove the RAID 5 and turn a RAID 1 for data in 2 HDs. SO, WAL and swap in the thrid HD. My questions: 1) I will see best disk performance changing the disk layout like above 2) HyperThreading really improve a procces basead program, like postgres Thank´s for all Alexandre ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Similar querys, better execution time on worst execution plan
Fernando,
1. Try EXPLAIN ANALYZE. Cost alone isn't an absolute measure. I think it's
only to see which parts of the query are expected to be slowest. However,
EXP ANA will give you exact times in msec (which effectively means it
executes the query).
2. I think calling upper() for each row costs more than direct comparison,
but not sure
3. Notice that there are seq scans with filter conditions like
id_instalacion = 2::numeric
Do you have indices on id_instalacion, which seems to be a numeric field?
if so, try casting the constant expressions in the query to numeric so that
postgresql may find the index. If you don't have such indices, it may be
worth to create them. (I guess you only have it on the table aliased with c,
since it does an index scan there.
4. another guess may be indices on (id_instalacion, activo), or, if activo
has few possible values (for example, it may be only one of three letters,
say, 'S', 'A' or 'K'), partial indices like:
CREATE INDEX cont_sbc_id_ins_S ON cont_sbc (id_instalacion)
WHERE activo in ('S', 's');
CREATE INDEX cont_sbc_id_ins_A ON cont_sbc (id_instalacion)
WHERE activo in ('A', 'a');
CREATE INDEX cont_sbc_id_ins_K ON cont_sbc (id_instalacion)
WHERE activo in ('K', 'k');
G.
--- cut here ---
WHERE c.id_instalacion = 2
AND s.id_instalacion = 2
AND p.id_instalacion = 2
...
- Seq Scan on cont_sbc s (cost=0.00..4.44 rows=1 width=35)
Filter: ((id_instalacion = 2::numeric)
AND (upper((activo)::text) = 'S'::text))
- Index Scan using cont_cont_cont_sbc_fk_i on cont_contenido c
(cost=0.00..15.56 rows=1 width=43)
Index Cond: ((c.id_instalacion = 2::numeric)
AND (c.id_sbc = outer.id_sbc))
Filter: (upper((activo)::text) = 'S'::text)
- Seq Scan on cont_publicacion p (cost=0.00..98.54 rows=442 width=55)
Filter: (id_instalacion = 2::numeric)
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
