Re: [GENERAL] Slow join over three tables
Hi!! what happens if you change your query to: SELECT r.id, r.age, r.gender, r.created, a.adverse, d.drug FROM reports r INNER JOIN report_drugs d USING (rid) INNER JOIN report_adverses a USING (rid) WHERE a.adverse = ANY (ARRAY['back pain - nonspecific', 'nonspecific back pain', 'back pain']) AND d.drug = ANY (ARRAY[359, 360, 361, 362, 363]) ORDER BY r.created; I have seen differences in time between "ON vs USING"i prefer "USING" when is possible > > > > All tables have indexes on the "id"/"rid" columns and on the > > "drug"/"adverse" columns. > > > > The query: > > > > SELECT r.id, r.age, r.gender, r.created, a.adverse, d.drug > > FROM reports r > > JOIN report_drugs d ON d.rid = r.id > > JOIN report_adverses a ON a.rid = r.id > > WHERE a.adverse = ANY (ARRAY['back pain - nonspecific', 'nonspecific back > > pain', 'back pain']) > > AND d.drug = ANY (ARRAY[359, 360, 361, 362, 363]) ORDER BY r.created; > > > I would suggest a few experiments to see how you can modify the plans > available to the optimizer: > > 1. CREATE INDEX ON report_drugs (drug, rid) > 2. CREATE INDEX ON report_adverses (adverse, rid) > 3. CREATE INDEX ON report (id, created) > > Re-run EXPLAIN ANALYZE of your query after each of these steps to see > how each one affects planning. You might also try two variants of the > query at each step, with and without the ORDER BY. > > Note, the index column order in (1) and (2) above is very important. > > > Karl > > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] query reboot pgsql 9.5.1
Hi!!! I try to explain my problem...sorry for my english :( In pgsql 9.5.1 I have a two tables with the next structure: 1. Tabla unlogged «public._gc_cat» Columna | Tipo | Modificadores -+--+--- idppicat| integer | idprodxintegrar | integer | tipo| character(1) | valor | numeric | estado | character(1) | idsll | text | idsfte | text | arama | text[] | ne_arama| integer | rama| text | rvar| text | nodec | integer | Índices: "_gc_cat_arama" btree (ne_arama) "_gc_cat_arama_gin" gin (arama) 2. Tabla unlogged «public._gc_tb» Columna | Tipo | Modificadores --+-+--- idb2 | integer | idc1 | integer | rama | text| arama| text[] | ne_arama | integer | Índices: "_gc_tb_arama" btree (ne_arama) "_gc_tb_arama_gin" gin (arama) "_gc_tb_idb2idc1" btree (idb2, idc1) the tabla _gc_cat have 91932 records an _gc_tb have 120130 records; when i run the next query: SELECT idprodxintegrar FROM _gc_tb a LEFT join _gc_cat b on ( b.arama <@ a.arama and a.arama < @ b.arama ) psql send the next message (after three minutes aprox.): Terminado (killed) and i have to reboot my "guest server". Now i execute the same in pgsql 9.4.5 and all is fine!!! The EXPLAINs are: - pgsql 9.5.1: Nested Loop Left Join (cost=0.03..492944.81 rows=276095 width=4) -> Seq Scan on _gc_tb a (cost=0.00..3321.30 rows=120130 width=66) -> Bitmap Heap Scan on _gc_cat b (cost=0.03..4.06 rows=2 width=70) Recheck Cond: ((arama <@ a.arama) AND (a.arama <@ arama)) -> Bitmap Index Scan on _gc_cat_arama_gin (cost=0.00..0.03 rows=2 width=0) Index Cond: ((arama <@ a.arama) AND (a.arama <@ arama)) - pgsql 9.4.5: Nested Loop Left Join (cost=0.03..492944.81 rows=276095 width=4) -> Seq Scan on _gc_tb a (cost=0.00..3321.30 rows=120130 width=66) -> Bitmap Heap Scan on _gc_cat b (cost=0.03..4.06 rows=2 width=70) Recheck Cond: ((arama <@ a.arama) AND (a.arama <@ arama)) -> Bitmap Index Scan on _gc_cat_arama_gin (cost=0.00..0.03 rows=2 width=0) Index Cond: ((arama <@ a.arama) AND (a.arama <@ arama)) If i change the query as: SELECT idprodxintegrar FROM _gc_tb a LEFT join _gc_cat b on ( a.ne_arama = b.ne_arama and a.arama <@ b.arama ) In pgsql 9.5.1 finished after 450708.112 ms In pgsql 9.4.5 finished after 17996.756 ms (very fast!!!) The EXPLAINs are: - pgsql 9.5.1 Nested Loop Left Join (cost=3.49..1915550.34 rows=41825277 width=4) -> Seq Scan on _gc_tb a (cost=0.00..3321.30 rows=120130 width=70) -> Bitmap Heap Scan on _gc_cat b (cost=3.49..14.39 rows=153 width=74) Recheck Cond: (a.arama <@ arama) Filter: (a.ne_arama = ne_arama) -> Bitmap Index Scan on _gc_cat_arama_gin (cost=0.00..3.45 rows=460 width=0) Index Cond: (a.arama <@ arama) - pgsql 9.4.5 Nested Loop Left Join (cost=3.48..1868759.71 rows=42284738 width=4) -> Seq Scan on _gc_tb a (cost=0.00..3321.30 rows=120130 width=70) -> Bitmap Heap Scan on _gc_cat b (cost=3.48..14.38 rows=115 width=74) Recheck Cond: (a.arama <@ arama) Filter: (a.ne_arama = ne_arama) -> Bitmap Index Scan on _gc_cat_arama_gin (cost=0.00..3.45 rows=460 width=0) Index Cond: (a.arama <@ arama) The shared_buffers and work_mem are the same in both versions of pgsql (128MB and 4MB) I am doing this test in a laptop with the next characteristics: - hp probook with 8 Gb ram. SATA disk, AMD A8-5550M - OS Linux (fedora 23) - lxc containers I am sharing the dumper's database are in the next links: http://sipre.inegi.org.mx/pgsql/dump_pgsql_9_4_5.dump http://sipre.inegi.org.mx/pgsql/dump_pgsql_9_5_1.dump similar post is found in: http://www.postgresql.org/message-id/CALrs2KPMowV6juLdOkMRq_P3MA5VkUmhdM4Q1OD0vCf2qimFfA@mail.gmail. com thanks in advance! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] puzzling perl DBI vs psql problem
De: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] en nombre de Susan Cassidy [susan.cass...@decisionsciencescorp.com] Enviado: jueves, 13 de marzo de 2014 02:48 p.m. Para: Rodrigo Gonzalez CC: Steve Atkins; pgsql-general@postgresql.org Asunto: Re: [GENERAL] puzzling perl DBI vs psql problem No, I don't have 2 instances running. I default the port on the psql command line, and the perl program is using 5432, as normal. Now, I'm discovering that syslog is no longer logging anything. I bounced it, but to no avail. Susan Check the log from webserver and check if the user is the same for psql and perl ... see you -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pl-perl for 64 bits in Solaris 9
Hi I don't have experience with solaris. I know it is a 64 bits: bash-2.05# isainfo -v 64-bit sparcv9 applications 32-bit sparc applications I tried to compile postgres (Version of postgres: 8.4.4 ) with plperl for 64 bits. The options for the configure are: /configure --with-CC=/usr/local/bin/gcc --with-perl --with-libxml --with-libxslt but when I do "make" get the next message: /port -L/usr/local/lib -o plpgsql.so make[4]: Leaving directory `/export/fmolina/pgsql/postgresql-8.4.4/src/pl/plpgsql/src' make[3]: Leaving directory `/export/fmolina/pgsql/postgresql-8.4.4/src/pl/plpgsql' make[3]: Entering directory `/export/fmolina/pgsql/postgresql-8.4.4/src/pl/plperl' /usr/local/bin/gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -fPIC -shared plperl.o spi_internal.o SPI.o -L/usr/lib/sparcv9 -L/usr/local/lib -L../../../src/port -L/usr/local/lib -m64 -lsocket -lnsl -ldl -lm -lc -Wl,-R'/usr/local/lib/perl5/5.8.9/sun4-solaris-64/CORE' -o plperl.so ld: fatal: file plperl.o: wrong ELF class: ELFCLASS32 ld: fatal: File processing errors. No output written to plperl.so collect2: ld returned 1 exit status make[3]: *** [plperl.so] Error 1 make[3]: Leaving directory `/export/fmolina/pgsql/postgresql-8.4.4/src/pl/plperl' make[2]: *** [all] Error 2 make[2]: Leaving directory `/export/fmolina/pgsql/postgresql-8.4.4/src/pl' make[1]: *** [all] Error 2 make[1]: Leaving directory `/export/fmolina/pgsql/postgresql-8.4.4/src' make: *** [all] Error 2 then i do: bash-2.05# file src/pl/plperl/plperl.o src/pl/plperl/plperl.o: ELF 32-bit MSB relocatable SPARC Version 1 My questions are: is possible compile postgres with pl-perl for 64? where i can read more about this problem? thank's -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] xpath
try to cast to "xml" xml_payload::xml
El mié, 10-02-2010 a las 12:39 +0300, Allan Kamau escribió:
> As advised by Peter,
> Below is an example (including the ddl and dml statements), it _drops_
> and creates a table called "simple_table" and a sequence called
> "simple_table_seq" both in the "public" schema.
>
> DROP SEQUENCE IF EXISTS simple_table_seq CASCADE;
> CREATE SEQUENCE simple_table_seq;
> DROP TABLE IF EXISTS simple_table CASCADE;
> CREATE TABLE simple_table
> (id INTEGER NOT NULL DEFAULT NEXTVAL('simple_table_seq')
> ,xml_payload TEXT
> ,PRIMARY KEY(id)
> )
> ;
> INSERT INTO simple_table
> (
> id
> ,xml_payload
> )
> SELECT
> nextval('simple_table_seq')AS id
> ,'some text' AS xml_payload
> ;
> SELECT a.id,a.xml_payload FROM simple_table a LIMIT 1;
> SELECT xpath('/doc/name/@first',SELECT a.xml_payload FROM simple_table
> a LIMIT 1);
> SELECT a.id,xpath('/doc/name/@first',a.xml_payload) FROM simple_table a LIMIT
> 1;
>
> SELECT xpath('/doc/name/@first',' last="Marston"/>some text');
>
> DROP SEQUENCE IF EXISTS simple_table_seq CASCADE;
> DROP TABLE IF EXISTS simple_table CASCADE;
>
>
> Allan.
>
> On Wed, Feb 10, 2010 at 11:34 AM, Otandeka Simon Peter
> wrote:
> > Allan,
> >
> > Postgres is very strict on variable types and char conversion. I have a
> > feeling you are trying to access data from a varchar feild using an
> > integer...
> >
> > Can you paste here your schema for that table?
> >
> > P.
> >
> > On Wed, Feb 10, 2010 at 11:06 AM, Allan Kamau wrote:
> >>
> >> Hi,
> >> I am running postgreSQL-8.4.2. I have a table that stores a single xml
> >> document per row in one of it's fields. I would like to use xpath to
> >> retrieve portions of these xml documents.
> >> Is there a way to do so. (I am running postgreSQL 8.4.2 configured
> >> (built) with --with-libxml and --with-libxslt options)
> >>
> >> I have looked at 'xpath' but I am unable to get it work for table fields.
> >>
> >> The command below works.
> >> SELECT xpath('/doc/name/@first',' >> last="Marston"/>...');
> >>
> >> The command below seems not to execute successfully
> >> SELECT a.id,xpath('/doc/name/@first',a.xml_payload) FROM
> >> staging.simple_table a WHERE a.id=1;
> >>
> >> HINT: No function matches the given name and argument types. You
> >> might need to add explicit type casts.
> >>
> >>
> >> Allan.
> >>
> >> --
> >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-general
> >
> >
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] triggers problems whit function
El mié, 22-10-2008 a las 15:01 -0500, Ma. Cristina Peña C. escribió: > I want to use a function in to a trigger > > > > This is my > > CREATE FUNCTION "subradio"(integer) RETURNS integer AS 'select > cast(count (claveubica) as integer ) from asradios where ubicacion > =0;' LANGUAGE 'sql'; > > > > And my ttrigger is > > CREATE TRIGGER validaradios AFTER DELETE ON subestacion FOR EACH ROW > EXECUTE PROCEDURE subradio(0); > > > > But I got an error > > ERROR: CreateTrigger: function subradio() does not exist > > > > What can I do?? write your function in plpgsql http://www.postgresql.org/docs/8.3/interactive/plpgsql-trigger.html > > > > > > cid:image002.jpg@01C8F24F.E2D90230 > > Ing. María Cristina Peña C. > > Analista Programador > > > > Sensa Control Digital S.A. de C.V. > > Dir. Av. Bravo #93 Ote. > > Col. Centro. > Torreón Coah. > > > > Tel. Directo: (871) 747 01 04 > > Conmutador: (871) 747 01 01 > > Tel. Fax:(871) 747 01 90 > > Correo Electrónico: [EMAIL PROTECTED] > > > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Tsearch2 - spanish
Hi
Thank's Teodor and Marcelo
the problem is solved
regards
-Mensaje original-
De: marcelo Cortez [mailto:[EMAIL PROTECTED]
Enviado el: jue 20/09/2007 7:13
Para: MOLINA BRAVO FELIPE DE JESUS; Teodor Sigaev
CC: PostgreSQL General
Asunto: Re: [GENERAL] Tsearch2 - spanish
Felipe
--- Felipe de Jesús Molina Bravo
<[EMAIL PROTECTED]> escribió:
> Hi
>
> You are rigth, the output of "show lc_ctype;" is C.
>
> Then I did is:
>
> prueba1=# show lc_ctype;
> lc_ctype
> -
> es_MX.ISO8859-1
> (1 row)
>
> and do it
>
> % initdb -D /YOUR/PATH -E LATIN1 --locale
> es_ES.ISO8859-1
>
> (how you do say)
>
> and "createdb -E iso8859-1 prueba1" and finally
> tsearch2
>
> the original problem is resolved
>
> prueba1=# select to_tsvector('espanol','melón');
> to_tsvector
> -
> 'melón':1
> (1 row)
>
>
> but if I change the sentece for it:
>
> prueba1=# select to_tsvector('espanol','melón perro
> mordelón');
> server closed the connection unexpectedly
> This probably means the server terminated
> abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting
> reset: Failed.
> !>
The same thing he same thing happened my to me at
first time with
Tsearch2 - spanish , i think you need
patch snowball with tsearch_snowball_82 file ,
googling
you find instructions how doit .
best regards
mdc
>
>
> ??? lost the connection ... the server is up
> any idea?
>
> The synonym is intentional
>
>
> thanks in advanced
>
>
> El mar, 18-09-2007 a las 21:40 +0400, Teodor Sigaev
> escribió:
> > > LC_CTYPE="POSIX"
> >
> >
> > pls, output of "show lc_ctype;" command. If it's C
> locale then I can identify
> > problem - characters diacritical mark (as ó) is
> not an alpha character, and
> > ispell dictionary will fail. To fix that you
> should run initdb with options:
> > % initdb -D /YOUR/PATH -E LATIN1 --locale
> es_ES.ISO8859-1
> > or
> > % initdb -D /YOUR/PATH -E UTF8 --locale es_ES.UTF8
> >
> > In last case you should also recode all
> dictionary's datafile in utf8 encoding.
> >
> > >>> prueba=# select
> to_tsvector('espanol','melón');
> > >>> ERROR: Affix parse error at 506 line
> > >> and
> > >>> prueba=# select lexize('sp','melón');
> > >>> lexize
> > >>> -
> > >>> {melon}
> > >>> (1 row)
> > sp is a Snowball stemmer, it doesn't require affix
> file, so it works.
> >
> > By the way, why is synonym dictionary paced after
> ispell? is it intentional?
> > Usually, synonym dictionary goes first, then
> ispell and after all of them snowball.
> >
>
> ---(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
>
Seguí de cerca a la Selección Argentina de Rugby en el Mundial de Francia
2007.
http://ar.sports.yahoo.com/mundialderugby
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
