Re: INDEX suggestion needed

On Thu, Dec 12, 2002 at 04:50:48PM +0100, Manfred Koizar wrote:
> On Thu, 12 Dec 2002 15:33:11 +0100, Thomas Beutin
> <tyrone(at)laokoon(dot)IN-Berlin(dot)DE> wrote:
> > AND ( visit >= '2002-12-01' OR visit <= '2002-12-11');
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> This is logically equivalent to (visit IS NOT NULL), I guess you want
> AND, not OR.
Yes, You are right, this is not my expected result =:-/
This must be an "AND".

> Please show us the output of
> VACUUM VERBOSE ANALYZE stat_pages;
tb=# VACUUM VERBOSE ANALYZE stat_pages;
NOTICE: --Relation stat_pages--
NOTICE: Pages 7809: Changed 0, Empty 0; Tup 343554: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.66s/0.12u sec elapsed 4.95 sec.
NOTICE: Analyzing stat_pages
VACUUM

> EXPLAIN ANALYZE
> SELECT COUNT(DISTINCT a_id)
> FROM stat_pages
> WHERE m_id = 35::smallint
> AND (visit >= '2002-12-01' AND visit <= '2002-12-11');
tb=# EXPLAIN ANALYZE SELECT COUNT(DISTINCT a_id) FROM stat_pages WHERE m_id = 35::smallint AND (visit >= '2002-12-01' AND visit <= '2002-12-11');
NOTICE: QUERY PLAN:

Aggregate (cost=3.19..3.19 rows=1 width=34) (actual time=52.89..52.89 rows=1 loops=1)
-> Index Scan using tb5 on stat_pages (cost=0.00..3.18 rows=1 width=34) (actual time=52.74..52.74 rows=0 loops=1)
Total runtime: 53.11 msec

EXPLAIN

This looks good, but look at this (first date changed):

tb=# EXPLAIN ANALYZE SELECT COUNT(DISTINCT a_id) FROM stat_pages WHERE m_id = 35::smallint AND (visit >= '2002-06-01' AND visit <= '2002-12-11');
NOTICE: QUERY PLAN:

Aggregate (cost=14679.91..14679.91 rows=1 width=34) (actual time=76204.22..76204.22 rows=1 loops=1)
-> Seq Scan on stat_pages (cost=0.00..13821.19 rows=343486 width=34) (actual time=375.61..5197.26 rows=343554 loops=1)
Total runtime: 76204.40 msec

EXPLAIN

i got the following index/sequence scans by date ranges:
(visit >= '2002-06-01' AND visit <= '2002-06-30') index scan
(visit >= '2002-06-01' AND visit <= '2002-07-31') index scan
(visit >= '2002-06-01' AND visit <= '2002-08-31') sequence scan
(visit >= '2002-07-01' AND visit <= '2002-07-31') index scan
(visit >= '2002-08-01' AND visit <= '2002-08-31') sequence scan
(visit >= '2002-09-01' AND visit <= '2002-09-30') sequence scan
(visit >= '2002-10-01' AND visit <= '2002-10-31') index scan, but long (>5sec)

And: The date range in my table is from 2002-07-10 11:36:53+02 up to
2002-10-29 23:31:47+01.

>
> SELECT attname, null_frac, avg_width, n_distinct,
> most_common_vals, histogram_bounds, correlation
> FROM pg_stats
> WHERE tablename = 'stat_pages'
> AND attname IN ('m_id', 'visit');
tb=# SELECT attname, null_frac, avg_width, n_distinct, most_common_vals, histogram_bounds, correlation FROM pg_stats WHERE tablename = 'stat_pages' AND attname IN ('m_id', 'visit');
attname | null_frac | avg_width | n_distinct | most_common_vals | histogram_bounds | correlation
---------+-----------+-----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
visit | 0 | 8 | -0.543682 | {"2002-08-21 10:29:10+02","2002-08-21 15:19:22+02","2002-08-29 17:29:41+02","2002-09-04 14:10:47+02","2002-09-11 09:45:48+02","2002-09-17 14:06:05+02","2002-09-17 16:24:59+02","2002-09-20 15:53:47+02","2002-09-23 13:46:59+02","2002-09-23 22:44:21+02"} | {"2002-07-25 16:37:12+02","2002-08-15 12:36:18+02","2002-08-23 12:36:15+02","2002-08-29 17:30:54+02","2002-09-05 12:54:31+02","2002-09-10 18:03:54+02","2002-09-16 15:44:56+02","2002-09-20 14:34:40+02","2002-09-24 13:59:29+02","2002-09-29 09:09:31+02","2002-10-29 23:25:13+01"} | -0.972118
m_id | 0 | 2 | 1 | {35} | | 1
(2 rows)

??? Is this output ok?

Regards,
-tb
--
Thomas Beutin tb(at)laokoon(dot)IN-Berlin(dot)DE
Beam me up, Scotty. There is no intelligent live down in Redmond.