Horton

Thread

  1. change owner on a table

    D. Duccini <duccini@backpack.com> — 2001-01-25T17:10:55Z

    i've got a table that was created by one user in a database, and i want to
change the owner to another

is there a straightforward way to do this?  or do i gotta hack the system
tables ??


-----------------------------------------------------------------------------
david@backpack.com            BackPack Software, Inc.        www.backpack.com
+1 651.645.7550 voice       "Life is an Adventure.    
+1 651.645.9798 fax            Don't forget your BackPack!"   
-----------------------------------------------------------------------------

  2. Indexes not used

    D. Duccini <duccini@backpack.com> — 2001-03-15T20:03:00Z

    Just upgraded from 6.5.3, on solaris

and i'm noticing that indexes are not being used by default

IE:  if i have a table

          Table "radusage"
 Attribute |    Type     | Modifier 
-----------+-------------+----------
 datetime  | timestamp   | not null
 account   | varchar(64) | 
 usage     | integer     | 
 sent      | integer     | 
 recv      | integer     | 
 ip        | bigint      | 
 host      | bigint      | 
 port      | smallint    | 
Indices: idxradaccount,
         idxraddate,
         idxradoid


and index idxradaccount is

# \d idxradaccount
  Index "idxradaccount"
 Attribute |    Type     
-----------+-------------
 account   | varchar(64)
btree


how come it doesn't use it if i

# explain select * from radusage where account = 'someuser';
NOTICE:  QUERY PLAN:

Seq Scan on radusage  (cost=0.00..13839.67 rows=5757 width=50)

EXPLAIN


????????????????



-----------------------------------------------------------------------------
david@backpack.com            BackPack Software, Inc.        www.backpack.com
+1 651.645.7550 voice       "Life is an Adventure.    
+1 651.645.9798 fax            Don't forget your BackPack!"   
-----------------------------------------------------------------------------

  3. Re: Indexes not used

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-15T20:27:38Z

    "D. Duccini" <duccini@backpack.com> writes:
> # explain select * from radusage where account = 'someuser';

> Seq Scan on radusage  (cost=0.00..13839.67 rows=5757 width=50)

Have you done a VACUUM ANALYZE?

The rows count looks like it might be a default estimate (but I'm just
counting on my fingers to guess that you have about a million rows in
the table).  If the rows count *is* accurate then a seq scan is
probably the right thing to be using...

			regards, tom lane

  4. PosgreSQL Windows install

    Jeff Williams <jeff.williams@hardsoft.com.au> — 2001-03-15T20:40:00Z

    I need some help please

Just downloaded the windows install from

http://208.160.255.143/pgsql/pgsql.exe

Installed it on my Windows ME machine
Installed it on Windows ME
Country/Region set to Australia
 
PosgreSQL/Guardian - will not start complains about the date
 
Country/Region set to United States
 
Question 1 = PosgreSQL - Starts.  Is there a way around this
======= 
 
With the Zero's Database explorer Logon
 
Database = template1
Login = sysdba

Question 2 = Password = ???? do you know what this is
========

Any help would be appreciated

Regards
Jeff Williams

  5. Re: Indexes not used

    D. Duccini <duccini@backpack.com> — 2001-03-15T20:42:04Z

    not even a million

# select count(*) from radusage;
 count  
--------
 573042
(1 row)

what is the analyze?  i've run vacuum several times

On Thu, 15 Mar 2001, Tom Lane wrote:

> "D. Duccini" <duccini@backpack.com> writes:
> > # explain select * from radusage where account = 'someuser';
> 
> > Seq Scan on radusage  (cost=0.00..13839.67 rows=5757 width=50)
> 
> Have you done a VACUUM ANALYZE?
> 
> The rows count looks like it might be a default estimate (but I'm just
> counting on my fingers to guess that you have about a million rows in
> the table).  If the rows count *is* accurate then a seq scan is
> probably the right thing to be using...
> 
> 			regards, tom lane
> 


-----------------------------------------------------------------------------
david@backpack.com            BackPack Software, Inc.        www.backpack.com
+1 651.645.7550 voice       "Life is an Adventure.    
+1 651.645.9798 fax            Don't forget your BackPack!"   
-----------------------------------------------------------------------------

  6. Re: Indexes not used

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-15T20:46:11Z

    "D. Duccini" <duccini@backpack.com> writes:
> # select count(*) from radusage;
>  count  
> --------
>  573042
> (1 row)

In that case 5757 is definitely a default estimate (.01 is the default
selectivity IIRC).

> what is the analyze?  i've run vacuum several times

VACUUM ANALYZE

			regards, tom lane

  7. Re: Indexes not used

    D. Duccini <duccini@backpack.com> — 2001-03-15T20:48:11Z

    > > what is the analyze?  i've run vacuum several times
> 
> VACUUM ANALYZE

i've done this as well, but it gives me no output other than "VACUUM"

or does it work silently in the background?

i've even dropped the index and recreated, but it didn't have any impact
on its use

-----------------------------------------------------------------------------
david@backpack.com            BackPack Software, Inc.        www.backpack.com
+1 651.645.7550 voice       "Life is an Adventure.    
+1 651.645.9798 fax            Don't forget your BackPack!"   
-----------------------------------------------------------------------------

  8. Re: Indexes not used

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-15T21:04:26Z

    "D. Duccini" <duccini@backpack.com> writes:
>> VACUUM ANALYZE

> i've done this as well, but it gives me no output other than "VACUUM"

That's what it's supposed to do.

> or does it work silently in the background?

If you want noise, try VACUUM VERBOSE ANALYZE.


Anyway: don't the EXPLAIN numbers change once you've done VACUUM
ANALYZE?  How many rows are there matching 'someuser', anyhow?

It might be useful to see the planner's statistics, too -- try

select attname,attdisbursion,s.*
from pg_statistic s, pg_attribute a, pg_class c
where starelid = c.oid and attrelid = c.oid and staattnum = attnum
and relname = 'FOO';

(substitute name of interesting table for FOO)

			regards, tom lane

  9. Re: Indexes not used

    David Olbersen <dave@slickness.org> — 2001-03-15T23:31:58Z

    Just as an example, here's the query plan of the *SAME* query before and after a
VACUUM ANALYZE

Notice the way the two plans are *COMPLETELY* different. l_portal_statuses and
b_portal_statuses only have *3 rows* right now, but there was no way for the
planner to know that. Anyway, this should be evidence that a good VACUUM ANALYZE
periodically is a Good Thing(tm).

(BTW, the site that this database drives is now significantly more responsive)

Before:
----------------------------------------
Merge Join  (cost=97.62..170.37 rows=1000 width=110)
  ->  Index Scan using l_portal_statuses_pkey on l_portal_statuses lps (cost=0.00..59.00 rows=1000 width=16)
  ->  Sort  (cost=97.62..97.62 rows=100 width=94)
    ->  Merge Join  (cost=22.67..94.30 rows=100 width=94)
      ->  Index Scan using b_portal_statuses_pkey on b_portal_statuses bps  (cost=0.00..59.00 rows=1000 width=16)
      ->  Sort  (cost=22.67..22.67 rows=10 width=78)
        ->  Seq Scan on contracts c  (cost=0.00..22.50 rows=10 width=78)

After:
----------------------------------------
Nested Loop  (cost=0.00..3.47 rows=1 width=110)
  ->  Nested Loop  (cost=0.00..2.40 rows=1 width=94)
    ->  Seq Scan on contracts c  (cost=0.00..1.34 rows=1 width=78)
    ->  Seq Scan on b_portal_statuses bps  (cost=0.00..1.03 rows=3 width=16)
  ->  Seq Scan on l_portal_statuses lps  (cost=0.00..1.03 rows=3 width=16)

-- Dave

  10. Re: Indexes not used

    D. Duccini <duccini@backpack.com> — 2001-03-16T14:26:14Z

    Maybe I'm not getting something here...but how is a sequential scan EVER
faster than a B-tree / index lookup on a database with over 500,000
records?

Certainly I could split out the data, and do some "roll-up" ops on the
information in there, it just seems odd that in 6.5.x it was using the
indices and was blazing fast

Now in 7.0.3 its like they are not even considered...at least on this
particular table....other tables they seem to be working

On Thu, 15 Mar 2001, David Olbersen wrote:

> Just as an example, here's the query plan of the *SAME* query before and after a
> VACUUM ANALYZE
> 
> Notice the way the two plans are *COMPLETELY* different. l_portal_statuses and
> b_portal_statuses only have *3 rows* right now, but there was no way for the
> planner to know that. Anyway, this should be evidence that a good VACUUM ANALYZE
> periodically is a Good Thing(tm).
> 
> (BTW, the site that this database drives is now significantly more responsive)
> 
> Before:
> ----------------------------------------
> Merge Join  (cost=97.62..170.37 rows=1000 width=110)
>   ->  Index Scan using l_portal_statuses_pkey on l_portal_statuses lps (cost=0.00..59.00 rows=1000 width=16)
>   ->  Sort  (cost=97.62..97.62 rows=100 width=94)
>     ->  Merge Join  (cost=22.67..94.30 rows=100 width=94)
>       ->  Index Scan using b_portal_statuses_pkey on b_portal_statuses bps  (cost=0.00..59.00 rows=1000 width=16)
>       ->  Sort  (cost=22.67..22.67 rows=10 width=78)
>         ->  Seq Scan on contracts c  (cost=0.00..22.50 rows=10 width=78)
> 
> After:
> ----------------------------------------
> Nested Loop  (cost=0.00..3.47 rows=1 width=110)
>   ->  Nested Loop  (cost=0.00..2.40 rows=1 width=94)
>     ->  Seq Scan on contracts c  (cost=0.00..1.34 rows=1 width=78)
>     ->  Seq Scan on b_portal_statuses bps  (cost=0.00..1.03 rows=3 width=16)
>   ->  Seq Scan on l_portal_statuses lps  (cost=0.00..1.03 rows=3 width=16)
> 
> -- Dave
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 


-----------------------------------------------------------------------------
david@backpack.com            BackPack Software, Inc.        www.backpack.com
+1 651.645.7550 voice       "Life is an Adventure.    
+1 651.645.9798 fax            Don't forget your BackPack!"   
-----------------------------------------------------------------------------

  11. Re: Indexes not used

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-16T16:09:22Z

    "D. Duccini" <duccini@backpack.com> writes:
> Maybe I'm not getting something here...but how is a sequential scan EVER
> faster than a B-tree / index lookup on a database with over 500,000
> records?

If the system needs to fetch more than a small percentage of the
records, then seqscan *will* be faster.  The issue you are dealing
with seems to be misestimation of the retrieval percentage for this
particular query, causing the planner to guess wrong about which
kind of plan to use.

			regards, tom lane

  12. Re: Indexes not used

    D. Duccini <duccini@backpack.com> — 2001-03-16T16:18:45Z

    > If the system needs to fetch more than a small percentage of the
> records, then seqscan *will* be faster.  The issue you are dealing
> with seems to be misestimation of the retrieval percentage for this
> particular query, causing the planner to guess wrong about which
> kind of plan to use.

no worries...i'll try building a subset of the data and see if there is
some "threshhold" value

or...maybe its time i actually contributed some code to the project :)

i built an OO database engine a few years ago (in objective-c) that used a
modified N-tree approach to indicies that massively accelerated the
retrieval of a lot of "highly similar" data items

-duck

-----------------------------------------------------------------------------
david@backpack.com            BackPack Software, Inc.        www.backpack.com
+1 651.645.7550 voice       "Life is an Adventure.    
+1 651.645.9798 fax            Don't forget your BackPack!"   
-----------------------------------------------------------------------------

  13. voodoo index usage ;)

    D. Duccini <duccini@backpack.com> — 2001-03-17T19:36:55Z

    perhaps the db gawds can explain this....

# \d radusage
          Table "radusage"
 Attribute |    Type     | Modifier 
-----------+-------------+----------
 datetime  | timestamp   | not null
 account   | varchar(64) | 
 usage     | integer     | 
 sent      | integer     | 
 recv      | integer     | 
 ip        | bigint      | 
 host      | bigint      | 
 port      | smallint    | 
Indices: idxradaccount,
         idxraddate,
         idxradoid

# \d idxradaccount
  Index "idxradaccount"
 Attribute |    Type     
-----------+-------------
 account   | varchar(64)
btree

# \d idxraddate
  Index "idxraddate"
 Attribute |   Type    
-----------+-----------
 datetime  | timestamp
btree

# explain select * from radusage where account = 'someuser';
NOTICE:  QUERY PLAN:

Seq Scan on radusage  (cost=0.00..13870.80 rows=5674 width=50)

and if i add in datetime (without effectively changing the semantic
meaning of the search)

# explain select * from radusage where account = 'someuser' and datetime >
'1900-01-01';
NOTICE:  QUERY PLAN:

Index Scan using idxradaccount on radusage  (cost=0.00..15295.37 rows=5668
width=50)


first case doesn't use the index, the second does use what would seem to
be the correct index

isn't that wacky????

-duck

-----------------------------------------------------------------------------
david@backpack.com            BackPack Software, Inc.        www.backpack.com
+1 651.645.7550 voice       "Life is an Adventure.    
+1 651.645.9798 fax            Don't forget your BackPack!"   
-----------------------------------------------------------------------------

  14. Re: voodoo index usage ;)

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-17T20:15:36Z

    "D. Duccini" <duccini@backpack.com> writes:
> # explain select * from radusage where account = 'someuser';
> NOTICE:  QUERY PLAN:

> Seq Scan on radusage  (cost=0.00..13870.80 rows=5674 width=50)

> and if i add in datetime (without effectively changing the semantic
> meaning of the search)

> # explain select * from radusage where account = 'someuser' and datetime >
> '1900-01-01';
> NOTICE:  QUERY PLAN:

> Index Scan using idxradaccount on radusage  (cost=0.00..15295.37 rows=5668
> width=50)


You could get more information by looking at the estimated cost of the
other alternative in each case (do SET ENABLE_SEQSCAN = OFF or SET
ENABLE_INDEXSCAN = OFF, respectively, to force the planner to choose the
other alternative).  I bet you'll find that the estimated costs are
pretty close together.  What's probably happening here is that the small
extra cost estimated for evaluating the "datetime > '1900-01-01'"
condition at each row is pushing the cost of the seqscan up to be more
than the cost of the indexscan.  That extra cost gets charged for every
row in the table in the seqscan case, but only for those rows pulled
from the index in the indexscan case, so adding extra WHERE conditions
favors the indexscan case.  Not by a lot, but evidently by enough in
this example.

			regards, tom lane