strange pg_stats behaviour?

From: Hubert depesz Lubaczewski <depesz(at)depesz(dot)pl>
To: pgsql-general(at)postgresql(dot)org
Subject: strange pg_stats behaviour?
Date: 2002-11-30 00:11:13
Message-ID: 20021130001113.GA9778@depesz.pl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

hi
i have table which contains number of field. one of them is:
data_off | timestamp with time zone |
with index:
hdl_auction_data_off btree (data_off)

statistics of this table are:
# select count(*) from auction;
count
-------
98792
(1 row)

# select count(distinct data_off) from auction;
count
-------
98558
(1 row)

now i want a query which will show me all records with old data_off:
SELECT aid, foto FROM auction WHERE data_off < now()::timestamptz - '31 days'::interval;

explain analyze is:
# explain analyze SELECT aid, foto FROM auction WHERE data_off < now() -
# '31 days'::interval;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Seq Scan on auction (cost=0.00..14985.38 rows=9493 width=11) (actual
time=21.33..1252.29 rows=1293 loops=1)
Filter: (data_off < (now() - '31 days'::interval))
Total runtime: 1253.61 msec
(3 rows)

of course estimated cost and rows are worth nothing. with enable_seqscan
false, i get total runtime below 30 msec!.
i tried to alter table auction alter column data_off set statistics 100,
200, and finally 1000.
of course i did analyze and reconnect.
no change. always very slow, seq scan. any idea on what is wrong?
right now information from pg_stats look like:
# select * from pg_stats where tablename='auction' and attname='data_off';
schemaname | tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation
------------+-----------+----------+-----------+-----------+------------+-----------------------------------+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
public | auction | data_off | 0 | 8 | -0.975561 | {"2002-11-19 12:53:58.540808+01"} | {0.00266667} | {"2002-10-29 13:52:44.757049+01","2002-11-01 19:36:40.123367+01","2002-11-04 21:59:39.391224+01","2002-11-10 03:56:53.322752+01","2002-11-14 08:00:27.357274+01","2002-11-18 20:55:26.336502+01","2002-11-22 09:44:31.660568+01","2002-11-26 10:40:08.078131+01","2002-11-30 17:12:04.360514+01","2002-12-05 08:37:02.211342+01","2002-12-20 00:44:18.810695+01"} | 0.196655
(1 row)

i'm testing it on postgresql 7.4devel, but on production server (7.2.2
if i recall correctly) it behaves exactly the same way.

i read all i could about optimising, and so on, but i can't figure out
what is wrong here. what am i missing?

depesz

--
hubert depesz lubaczewski http://www.depesz.pl/
------------------------------------------------------------------------
Mój Boże, spraw abym milczał, dopóki się nie upewnię, że naprawdę mam
coś do powiedzenia. (c) 1998 depesz

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Hubert depesz Lubaczewski 2002-11-30 00:20:01 Re: The old "not using index" question
Previous Message Christopher Kings-Lynne 2002-11-29 23:31:56 Re: 7.4 Wishlist