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@laokoon.IN-Berlin.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'
ANDvisit <= '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'
ANDvisit <= '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
WHEREtablename = '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-0414: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-2015: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-1512: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-1018: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-2909: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@laokoon.IN-Berlin.DE
Beam me up, Scotty. There is no intelligent live down in Redmond.