[PERFORM] started Data Warehousing
Hello people! I have a question, I am going to begin a project for the University in the area of Data Warehousing and I want to use postgres. Do you have some recommendation to me? Thanks!! Greetings, Pablo ---(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] [ADMIN] syslog slowing the database?
Not an option I'm afraid. PostgreSQL just jams and stops logging after the first rotation... Are you using a copy truncate method to rotate the logs? In RedHat add the keyword COPYTRUCATE to your /etc/logrotate.d/syslog file. Sincerely, Joshua D. Drake I know some people use this in production. Dunno what went wrong in your test, but it can be made to work. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0034 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] started Data Warehousing
Pablo Marrero wrote: Hello people! I have a question, I am going to begin a project for the University in the area of Data Warehousing and I want to use postgres. Do you have some recommendation to me? Regarding what? Do you have an specific questions? Sincerely, Joshua D. Drake Thanks!! Greetings, Pablo ---(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 begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0034 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] compiling 7.4.1 on Solaris 9
On Wed, Mar 10, 2004 at 11:07:28AM -0500, Andrew Sullivan wrote: At work, we have been doing a number of tests on 7.4. The performance is such an improvement over 7.2 that the QA folks thought there must be something wrong. So I suppose the defaults are ok. I know, I know, replying to myself. I just wanted to note that we _were_ using optimisation with 7.2. 7.4 is still a lot faster. A -- Andrew Sullivan | [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] Impact of varchar/text in use of indexes
Hi. I have two existing tables, A and B. A has a 'varchar(1000)' field and B has a 'text' field, each with btree indexes defined. When I do a join between these, on this field, it seems to a hash join, as opposed to using the indexes, as I might expect (I'm no postgres expert, btw). My question is: if I changed both fields to be text or varchar(1000) then would the index be used? Ta, -- Mike ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Impact of varchar/text in use of indexes
Mike Moran [EMAIL PROTECTED] writes: Hi. I have two existing tables, A and B. A has a 'varchar(1000)' field and B has a 'text' field, each with btree indexes defined. When I do a join between these, on this field, it seems to a hash join, as opposed to using the indexes, as I might expect (I'm no postgres expert, btw). My question is: if I changed both fields to be text or varchar(1000) then would the index be used? Probably not, and in any case your assumption is mistaken. Indexes are not always the right way to join. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Sorting when LEFT JOINING to 2 same tables, even aliased.
Hello to everybody. I ask your help for a severe problem when doing a query that LEFT JOINs one table to another ON a field, and then LEFT JOINs again to another instance of a table ON another field which stores the same entity, but with different meaning. I include 3 EXPLAIN ANALYZEs: * The first one, the target (and problematic) query, which runs in 5 to 6 minutes. * The second one, a variation with the second LEFT JOIN commented out, which runs in 175 to 450 ms. * The third one, a variation of the first one with ORDER BY removed, which gives me about 19 seconds. Therefore, I feel like there are two problems here the one that raises the clock to 6 minutes and one that raises it to 20 seconds. I expected a much lower time. I checked indexes and data types already, they are all fine. All relevant fields have BTREEs, all PKs have UNIQUE BTREE, and all id and ext_* fields have 'integer' as data type. Each ext_* has its corresponding REFERENCES contraint. I translated all the table and field names to make it easier to read. I made my best not to let any typo go through. I'd appreciate any help. Octavio. === First EXPLAIN ANALYZE === EXPLAIN ANALYZE SELECT t_materias_en_tira.id AS Id, t_clientes.paterno || ' ' || t_clientes.materno || ' ' || t_clientes.nombre AS Alumno, t_materias.nombre AS Materia, t_materias__equivalentes.nombre AS MateriaEquivalente, t_grupos.nombre AS Grupo, calificacion_final AS Calificacion, tipo AS Tipo, eer AS EER, total_asistencias AS TotalAsistencias, total_clases As TotalClases FROM t_materias_en_tira LEFT JOIN t_alumnos_en_semestre ON ext_alumno_en_semestre = t_alumnos_en_semestre.id LEFT JOIN t_alumnos ON ext_alumno = t_alumnos.id LEFT JOIN t_clientes ON ext_cliente = t_clientes.id LEFT JOIN t_materias ON ext_materia = t_materias.id LEFT JOIN t_materias AS t_materias__equivalentes ON ext_materia__equivalencia = t_materias.id LEFT JOIN t_grupos ON ext_grupo = t_grupos.id WHERE t_alumnos_en_semestre.ext_ciclo = ORDER BY Alumno, Materia; This one gave: QUERY PLAN -- Sort (cost=11549.08..11552.11 rows=1210 width=112) (actual time=311246.000..355615.000 rows=1309321 loops=1) Sort Key: (t_clientes.paterno)::text || ' '::text) || (t_clientes.materno)::text) || ' '::text) || (t_clientes.nombre)::text), t_materias.nombre InitPlan - Result (cost=0.00..0.01 rows=1 width=0) (actual time=2.000..2.000 rows=1 loops=1) - Hash Left Join (cost=1089.25..11487.11 rows=1210 width=112) (actual time=83.000..19303.000 rows=1309321 loops=1) Hash Cond: (outer.ext_grupo = inner.id) - Nested Loop Left Join (cost=1086.92..11454.53 rows=1210 width=107) (actual time=82.000..9077.000 rows=1309321 loops=1) Join Filter: (outer.ext_materia__equivalencia = outer.id) - Hash Left Join (cost=1078.15..1181.93 rows=1210 width=93) (actual time=82.000..275.000 rows=3473 loops=1) Hash Cond: (outer.ext_materia = inner.id) - Merge Right Join (cost=1068.43..1154.07 rows=1210 width=71) (actual time=81.000..213.000 rows=3473 loops=1) Merge Cond: (outer.id = inner.ext_cliente) - Index Scan using t_clientes_pkey on t_clientes (cost=0.00..62.87 rows=1847 width=38) (actual time=10.000..34.000 rows=1847 loops=1) - Sort (cost=1068.43..1071.46 rows=1210 width=41) (actual time=71.000..76.000 rows=3473 loops=1) Sort Key: t_alumnos.ext_cliente - Hash Left Join (cost=41.12..1006.48 rows=1210 width=41) (actual time=9.000..61.000 rows=3473 loops=1) Hash Cond: (outer.ext_alumno = inner.id) - Nested Loop (cost=0.00..944.18 rows=1210 width=41) (actual time=3.000..36.000 rows=3473 loops=1) - Index Scan using i_t_alumnos_en_semestre__ext_ciclo on t_alumnos_en_semestre (cost=0.00..8.63 rows=269 width=8) (actual time=2.000..3.000 rows=457 loops=1) Index Cond: (ext_ciclo = $0) - Index Scan using i_t_materias_en_tira__ext_alumno_en_semestre on t_materias_en_tira (cost=0.00..3.32 rows=12 width=41) (actual time=0.009..0.035 rows=8 loops=457) Index Cond: (t_materias_en_tira.ext_alumno_en_semestre = outer.id) - Hash (cost=36.50..36.50 rows=1850 width=8) (actual
Re: [PERFORM] Sorting when LEFT JOINING to 2 same tables, even
On Thu, 11 Mar 2004, Octavio Alvarez wrote: Hello to everybody. I ask your help for a severe problem when doing a query that LEFT JOINs one table to another ON a field, and then LEFT JOINs again to another instance of a table ON another field which stores the same entity, but with different meaning. I include 3 EXPLAIN ANALYZEs: * The first one, the target (and problematic) query, which runs in 5 to 6 minutes. * The second one, a variation with the second LEFT JOIN commented out, which runs in 175 to 450 ms. * The third one, a variation of the first one with ORDER BY removed, which gives me about 19 seconds. Therefore, I feel like there are two problems here the one that raises the clock to 6 minutes and one that raises it to 20 seconds. I expected a much lower time. I checked indexes and data types already, they are all fine. All relevant fields have BTREEs, all PKs have UNIQUE BTREE, and all id and ext_* fields have 'integer' as data type. Each ext_* has its corresponding REFERENCES contraint. I translated all the table and field names to make it easier to read. I made my best not to let any typo go through. I'd appreciate any help. This join filter Join Filter: (outer.ext_materia__equivalencia = outer.id) which I believe belongs to LEFT JOIN t_materias AS t_materias__equivalentes ON ext_materia__equivalencia = t_materias.id seems wrong. Did you maybe mean = t_materias__equivalentes.id there? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
