valid use of wildcard - Mailing list pgsql-general

Hi,

Is the following query a valid use of the 'wildcard' in (='2008-10-27%')?

> [arcsoft@dsan4 arcsoft]$ psql metadata
> Password:
> Welcome to psql 8.1.9, the PostgreSQL interactive terminal.
>
> metadata=# SELECT * FROM viewspace.siap AS t WHERE t."startDate"='2008-10-27%' AND t.prop_id LIKE '%' LIMIT 1000;

Causes the %CPU to jump and process lingers for over an hour.

> Processes:  87 total, 3 running, 84 sleeping... 321 threads            15:51:49
> Load Avg:  0.28, 0.28, 0.24     CPU usage:  11.4% user, 9.1% sys, 79.5% idle
> SharedLibs: num =  164, resident = 29.5M code, 4.52M data, 7.30M LinkEdit
> MemRegions: num = 10409, resident =  311M + 13.8M private,  501M shared
> PhysMem:   750M wired,  125M active, 1.42G inactive, 2.27G used, 1.73G free
> VM: 13.2G + 97.3M   30039(0) pageins, 0(0) pageouts
>
>   PID COMMAND      %CPU   TIME   #TH #PRTS #MREGS RPRVT  RSHRD  RSIZE  VSIZE
> 10637 postgres    69.1%  0:17.43   1     9    52  7.60M-  433M  56.9M- 1.06G
> 10635 psql         0.0%  0:00.00   1    14    22   256K+  608K   728K+ 27.2M
> 10634 top          9.1%  0:03.96   1    21    20   492K   396K   976K  27.0M
> 10633 bash         0.0%  0:00.00   1    14    16   204K   792K   808K  27.1M
> 10632 sshd         0.0%  0:00.00   1    11    45   116K  1.58M   516K  30.0M
> 10628 sshd         0.0%  0:00.09   1    18    46   144K  1.58M  1.47M  30.1M
> 10562 postgres     0.0%  0:43.65   1     9    30  1.30M   433M  64.8M  1.05G
> 10559 psql         0.0%  0:00.03   1    14    23   252K   608K   736K  27.2M

I do a 'reindexdb -d metadata' and re-run same query and get a response
back quickly:

> [arcsoft@dsan4 arcsoft]$ psql metadata
> Password:
> Welcome to psql 8.1.9, the PostgreSQL interactive terminal.
>
>
> metadata=# SELECT * FROM viewspace.siap AS t WHERE t."startDate"='2008-10-27%' AND t.prop_id LIKE '%' LIMIT 1000;
>  image_id  |     reference     | fits_extension |  object   |  prop_id   |      startDate      |      ra
>      |      dec      | equinox | numberOfAxes | naxis_length |  scale  |  mimeType  | instrument | telesco
> pe | cprojection | crefpixel | crefvalue | cdmatrix | fileSize  | pixflags |     bandpass_id     | bandpas
> s_unit | bandpass_lolimit | bandpass_hilimit | exposure |  depth  | depthErr | seeing  |     releaseDate
>    | vo_id
> -----------+-------------------+----------------+-----------+------------+---------------------+----------
> -----+---------------+---------+--------------+--------------+---------+------------+------------+--------
> ---+-------------+-----------+-----------+----------+-----------+----------+---------------------+--------
> -------+------------------+------------------+----------+---------+----------+---------+------------------
> ---+-------
>  ct1417659 | ct1417659.fits.gz |              1 | object    | noao       | 2008-10-27 00:00:00 | 14:59:22.
> 49   | -30:08:17.49  |  2000.0 |            2 | unknown      | unknown | image/fits | mosaic_2   | ct4m
>    | unknown     | unknown   | unknown   | unknown  |  88343772 | unknown  | VR Supermacho c6027 | unknown
>        | unknown          | unknown          | 1.000    | unknown | unknown  | unknown | 2010-04-27 00:00:
> 00 |
>  ct1417660 | ct1417660.fits.gz |              1 | unknown   | smarts     | 2008-10-27 00:00:00 | 18:05:49.
> 42   | -19:26:22.6   |  2000.0 |            2 | unknown      | unknown | image/fits | ccd_spec   | ct15m
>    | unknown     | unknown   | unknown   | unknown  |    270250 | unknown  | CuSO4               | unknown
>        | unknown          | unknown          | 0.000    | unknown | unknown  | unknown | 2010-04-27 00:00:
> 00 |
>  ct1417661 | ct1417661.fits.gz |              1 | unknown   | smarts     | 2008-10-27 00:00:00 | 18:06:02.
> 66   | -19:26:22.8   |  2000.0 |            2 | unknown      | unknown | image/fits | ccd_spec   | ct15m
>    | unknown     | unknown   | unknown   | unknown  |    269673 | unknown  | CuSO4               | unknown
>

Why does reindexdb help?
How is WHERE t."startDate"='2008-10-27%' getting interpreted?

Thank you.
-- irene
---------------------------------------------------------------------
Irene Barg                    Email:  ibarg@noao.edu
NOAO/AURA Inc.                 http://www.noao.edu/noao/staff/ibarg
950 N. Cherry Ave.            Voice:  520-318-8273
Tucson, AZ  85726 USA           FAX:  520-318-8360
---------------------------------------------------------------------