
> > Also, I would like to know whether or not the following is an acceptable
> > way to get ecpg.dll linked against libpgtypes.a:
> > 
> >     +++ interfaces/ecpg/ecpglib/Makefile    2003/04/29 17:40:25
> >     @@ -22,6 +22,9 @@ OBJS= execute.o typename.o descriptor.o 
> >         connect.o misc.o
> > 
> >      SHLIB_LINK= $(libpq)
> >     +ifeq ($(PORTNAME), cygwin)
> >     +SHLIB_LINK+= -L../pgtypeslib -lpgtypes
> >     +endif
> > 
> > If not, what is the recommended approach?

I think AIX will need this too. Anyway I can't check it, since 
the snapshots are not produced anymore (last is from 24.3.) (Marc ?) :-(
(I am behind a firewall here, no reliable socks proxy available)

Andreas


Wed, 30 Apr 2003 12:35:35 +0200
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: Cygwin PostgreSQL CVS build issues
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
> I think AIX will need this too. Anyway I can't check it, since 
> the snapshots are not produced anymore (last is from 24.3.) (Marc ?) :-(

That's fixed as of last night (thanks Marc) --- seems cvs.postgresql.org
had an old bison and so the build was failing.

			regards, tom lane


Wed, 30 Apr 2003 10:26:11 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Cygwin PostgreSQL CVS build issues 
This seems an old topic to the list,
but it's one of the confusing area for
postgresql users -

example table:

1. about "like" and "similar to" operators
postgresql can't use index if your database
encoding is anything other than 'C'. After I did
this, I knew this will not take care of
'%pattern%' map, which requires the text search module.

question 1: query uses the index, why the performance is still bad?

emrxdbs=# explain analyze select * from patient where lastname like 'BEC%';
                                                             QUERY PLAN
----------------------------------------------------------------------------
--------------------------
 Index Scan using patient_lname_idx on patient  (cost=0.00..251.65 rows=62
width=176) (actual time=63.48..8860.14 rows=1732 loops=1)
   Index Cond: ((lastname >= 'BEC'::character varying) AND (lastname <
'BED'::character varying))
   Filter: (lastname ~~ 'BEC%'::text)
 Total runtime: 8870.27 msec

emrxdbs=#  explain analyze select * from patient where firstname like
'RAN%';
                                                             QUERY PLAN
----------------------------------------------------------------------------
--------------------------
 Index Scan using patient_fname_idx on patient  (cost=0.00..18.25 rows=4
width=176) (actual time=39.84..12515.44 rows=2649 loops=1)
   Index Cond: ((firstname >= 'RAN'::character varying) AND (firstname <
'RAO'::character varying))
   Filter: (firstname ~~ 'RAN%'::text)
 Total runtime: 12532.38 msec

question 2: why "not like" is not using index?

emrxdbs=#  explain analyze select * from patient where firstname not like
'MAR%';
                                                    QUERY PLAN
----------------------------------------------------------------------------
---------------------------------------
 Seq Scan on patient  (cost=0.00..20102.05 rows=745897 width=176) (actual
time=0.09..76515.69 rows=717610 loops=1)
   Filter: (firstname !~~ 'MAR%'::text)
 Total runtime: 81983.46 msec
(3 rows)


2. about POSIX Regular Expressions, it doesn't use index at all?!
emrxdbs=# explain analyze select * from patient where firstname ~ 'RAN%';
                                                   QUERY PLAN
----------------------------------------------------------------------------
------------------------------------
 Seq Scan on patient  (cost=0.00..20102.05 rows=5985 width=176) (actual
time=25040.93..25040.93 rows=0 loops=1)
   Filter: (firstname ~ 'RAN%'::text)
 Total runtime: 25041.23 msec
(3 rows)

3. what's the general guidelines in terms of simple pattern
match in postgresql (not the text search module)?

Can someone help to clear the above questions?

thanks.

johnl
============================================================================
========
exmple table - count=750000
     Column     |            Type             |
Modifiers
----------------+-----------------------------
 patseq         | integer                     | not null default
nextval('public.patient_patseq_seq'::text)
 mpin           | character varying(12)       | not null
 mrn            | character varying(18)       | not null
 facilityid     | character varying(12)       | not null
 dob            | date                        |
 sex            | character(1)                |
 lastname       | character varying(30)       |
 firstname      | character varying(30)       |
 middlename     | character varying(30)       |
 terminatedcare | boolean                     |
 iscurrent      | boolean                     | not null
 fielda         | character varying(10)       |
 fieldb         | character varying(10)       |
 fieldc         | character varying(10)       |
 fieldd         | character varying(10)       |
 fielde         | character varying(10)       |
 fieldf         | timestamp without time zone | default
('now'::text)::timestamp(6) with time zone
Indexes: patient_pkey primary key btree (patseq),
         patient_fname_idx btree (firstname),
         patient_lname_idx btree (lastname),
         patient_mpin_idx btree (mpin),
         patient_mrnfac_idx btree (mrn, facilityid)


Wed, 30 Apr 2003 10:13:28 -0500
"John Liu" <johnl@emrx.com>
pattern match,  index use and performance