Thread: Data comparison SQL in PG 8.2.9
Hi. I have a large table that has a modify_date field in it, which is
timestamp without time zone.
I used to be able to do queries like these in 8.2.3 --
select id from users where modify_date = '2009-01-08' limit 1;
select id from users where modify_date > '2009-01-08' limit 1;
Suddenly these are returning:
id
----
(0 rows)
Time: 11.635 ms
I can see through other SQL that there are rows with these dates in
them! My "\d users" shows these two relevant entries about
modify_date:
Table "public.users"
Column | Type |
Modifiers
-----------------------+-----------------------------+------------------------------
modify_date | timestamp without time zone |
....
Indexes:
"new_idx_modify_date" btree (modify_date) WITH (fillfactor=75)
Any ideas why? I ran an explain analyze and this is the output:
******
=# explain analyze select id from users where modify_date =
'2009-01-08' limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..0.37 rows=1 width=8) (actual time=0.082..0.082
rows=0 loops=1)
-> Index Scan using new_idx_modify_date on users (cost=0.00..4.12
rows=11 width=8) (actual time=0.074..0.074 rows=0 loops=1)
Index Cond: (modify_date = '2009-01-08 00:00:00'::timestamp
without time zone)
Total runtime: 19.484 ms
(4 rows)
Time: 19.940 ms
******
Another weird thing is that a query that has ">" a certain
modify_date, even just yesterday as the date condition, takes a LONG
time and is almost unusable.
Appreciate any pointers.
Thx!
be sure to use correct data types. I suppose psql uses timestamps so select id from users where modify_date = '2009-01-08' limit 1; is converted to select id from users where modify_date = '2009-01-08 00:00:00'::timestamp limit 1; try select id from users where modify_date::date = '2009-01-08'::date limit 1; regards thomas Phoenix Kiula schrieb: > Hi. I have a large table that has a modify_date field in it, which is > timestamp without time zone. > > I used to be able to do queries like these in 8.2.3 -- > > select id from users where modify_date = '2009-01-08' limit 1; > select id from users where modify_date > '2009-01-08' limit 1; > > Suddenly these are returning: > > id > ---- > (0 rows) > > Time: 11.635 ms > > I can see through other SQL that there are rows with these dates in > them! My "\d users" shows these two relevant entries about > modify_date: > > > Table "public.users" > Column | Type | > Modifiers > -----------------------+-----------------------------+------------------------------ > modify_date | timestamp without time zone | > .... > Indexes: > "new_idx_modify_date" btree (modify_date) WITH (fillfactor=75) > > > Any ideas why? I ran an explain analyze and this is the output: > > > ****** > =# explain analyze select id from users where modify_date = > '2009-01-08' limit 1; > QUERY PLAN > ---------------------------------------------------------------------------------------------------------------------------------- > Limit (cost=0.00..0.37 rows=1 width=8) (actual time=0.082..0.082 > rows=0 loops=1) > -> Index Scan using new_idx_modify_date on users (cost=0.00..4.12 > rows=11 width=8) (actual time=0.074..0.074 rows=0 loops=1) > Index Cond: (modify_date = '2009-01-08 00:00:00'::timestamp > without time zone) > Total runtime: 19.484 ms > (4 rows) > > Time: 19.940 ms > ****** > > Another weird thing is that a query that has ">" a certain > modify_date, even just yesterday as the date condition, takes a LONG > time and is almost unusable. > > Appreciate any pointers. > > Thx! > > -- Thomas Markus ==================================================== proventis GmbH | Zimmerstr. 79-81 | D-10117 Berlin | Tel +49 (0)30 2936399-22 | Fax -50 | t.markus@proventis.net ----------------------------------------------------------------- Geschäftsführer: Norman Frischmuth | Sitz: Berlin Handelsregister: AG Berlin-Charlottenburg, HR 82917 ----------------------------------------------------------------- Blue Ant-webbasiertes Projektmanagement - aktuelle Termine 2008: http://www.proventis.net/website/live/blueant/veranstaltungen.html ====================================================
Attachment
2009/1/12 Thomas Markus <t.markus@proventis.net>: > be sure to use correct data types. I suppose psql uses timestamps so > > select id from users where modify_date = '2009-01-08' limit 1; > > is converted to > > select id from users where modify_date = '2009-01-08 00:00:00'::timestamp > limit 1; > > > try > > select id from users where modify_date::date = '2009-01-08'::date limit 1; Thanks. But it used to work without this, and more importantly, this doesn't explain why the ">" queries are so exceedingly slow now! Any thoughts?
check explain analyze tried this on 8.3 with real life data: -- all rows, index useless explain analyze select id from cl_customer where modified > '2008-01-01' Seq Scan on cl_customer (cost=0.00..38958.79 rows=1448639 width=8) (actual time=0.030..682.940 rows=1448783 loops=1) Filter: (modified > '2008-01-01 00:00:00'::timestamp without time zone) Total runtime: 1015.394 ms -- small subset explain analyze select id from cl_customer where modified > '2009-01-01' Index Scan using i_cl_customer_modified on cl_customer (cost=0.00..12.93 rows=144 width=8) (actual time=0.018..0.110 rows=175 loops=1) Index Cond: (modified > '2009-01-01 00:00:00'::timestamp without time zone) Total runtime: 0.169 ms Phoenix Kiula schrieb: > Thanks. But it used to work without this, and more importantly, this > doesn't explain why the ">" queries are so exceedingly slow now! Any > thoughts? >
Attachment
On Monday 12 January 2009 09:40:22 Phoenix Kiula wrote: > 2009/1/12 Thomas Markus <t.markus@proventis.net>: > Thanks. But it used to work without this, and more importantly, this > doesn't explain why the ">" queries are so exceedingly slow now! Any > thoughts? When queries used to be fast and now are slow very often depends upon the indexes. Less frequently upon the amount of memory available for cache and the server configuration. Do you used ti have any index on that column? Do you have server configuration files for comparison? -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand
> When queries used to be fast and now are slow very often depends upon the > indexes. Less frequently upon the amount of memory available for cache and > the server configuration. > Do you used ti have any index on that column? > Do you have server configuration files for comparison? The config file is exactly the samea s the old PG version (8.2.3 --> 8.2.9). All other queries work super fast. Perhaps faster in 8.2.9. Only the date fields seem slower. Yes, as I mentioned in my post the field is indexed (btree with fill factor of Thomas, my slow queries are from a date that was yesterday, as I mentioned. A ">" condition even for just yesterday takes about 10-15 seconds, where it used to take less than a second before.
On Monday 12 January 2009 10:18:59 Phoenix Kiula wrote: > > When queries used to be fast and now are slow very often depends upon the > > indexes. Less frequently upon the amount of memory available for cache > > and the server configuration. > > Do you used ti have any index on that column? > > Do you have server configuration files for comparison? > > The config file is exactly the samea s the old PG version (8.2.3 --> > 8.2.9). > > All other queries work super fast. Perhaps faster in 8.2.9. Only the > date fields seem slower. > > Yes, as I mentioned in my post the field is indexed (btree with fill factor > of > > Thomas, my slow queries are from a date that was yesterday, as I > mentioned. A ">" condition even for just yesterday takes about 10-15 > seconds, where it used to take less than a second before. I possible run the EXPLAIN ANALYZE on both DBs and either compare or post the results. If you find sequential scans with tests using that column, then something is definitely needing investigation. In a quick test I did dates and timestamps work great with indexes. -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand
Phoenix Kiula wrote: > Table "public.users" > Column | Type | > Modifiers > -----------------------+-----------------------------+------------------------------ > modify_date | timestamp without time zone | > .... > Indexes: > "new_idx_modify_date" btree (modify_date) WITH (fillfactor=75) > > > Any ideas why? I ran an explain analyze and this is the output: > Another weird thing is that a query that has ">" a certain > modify_date, even just yesterday as the date condition, takes a LONG > time and is almost unusable. Have you tried REINDEX? VACUUM ANALYZE since the update? Not sure the security fix relating to indexes in 8.2.6 affects you. Did the suggestion of adding the type cast affect the query? If so then maybe the planner is now running the type conversion per row instead of a one off from the constant in the select. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz