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@xxxxxxxx
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
---------------------------------------------------------------------
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
