Thread: some questions

some questions

From
Ang Tun Chek
Date:
hi   i am new to postresql and i need some help for some sql command
   what is the sql command in postresql equal to "describe table"?
   what is the sql command to list all tables in the database?
  thank you



Re: some questions

From
"Christopher Kings-Lynne"
Date:
> hi
>     i am new to postresql and i need some help for some sql command
>
>     what is the sql command in postresql equal to "describe table"?
>
>     what is the sql command to list all tables in the database?
>
>    thank you

OK, if you're using psql, you can go '\dt' to see all the tables or '\d
tablename' to see table info, but if you want to query them yourself, you'll
need to query the system catalogs:

http://www.au.postgresql.org/users-lounge/docs/7.2/postgres/catalogs.html

eg. SELECT * FROM pg_tables;

Chris



Re: some questions

From
Adam Witney
Date:
From within psql

>   what is the sql command in postresql equal to "describe table"?

\d "tablename"

>   what is the sql command to list all tables in the database?

\dt

More info and other commands can be found by typing \?

adam




Help on indexes

From
Stephane DEWITTE
Date:
Hi everybody.

I have some little problem on index utilisation.

Here is the schema of my base :

-----------------------------------                 Table "mclis"  Column    |         Type          | Modifiers
-------------+-----------------------+-----------cli_cod     | numeric(9,0)          | not nullcli_civ_cod | character
varying(5) | not nullcli_nomu    | character varying(32) | not nullcli_pren    | character varying(32) | not
nullcli_dnai   | date                  | not nullcli_insee   | character varying(13) | not nullcli_cle     | character
varying(2) | not nullcli_mdp_def | character varying(6)  | not null
 
Indexes: cli_cod_mclis_key,        cli_insee_mclis_key,        cli_nomu_mclis_key
Primary key: mclis_pkey

                Table "mdrs"  Column   |         Type         | Modifiers
------------+----------------------+-----------dr_cod     | numeric(11,0)        | not nulldr_typ     | character
varying(1)| not nulldr_dcre    | date                 | not nulldr_dmaj    | date                 | not nulldr_mnap
|numeric(18,6)        | not nulldr_typdrg  | character varying(1) | not nulldr_cli_cod | numeric(9,0)         |dr_dpai
 | date                 |dr_mdr_cod | character varying(3) |dr_psa_cod | numeric(6,0)         |
 
Indexes: dr_cli_cod_mdrs_key,        dr_cod_mdrs_key,        dr_psa_cod_mdrs_key,        idx_dr_dpai,
idx_dr_mnap
Primary key: mdrs_pkey

                Table "mdecs"   Column    |         Type          | Modifiers
--------------+-----------------------+-----------dec_cod      | character varying(20) | not nulldec_dcre     | date
             | not nulldec_typedec  | character varying(1)  | not nulldec_deccomp  | character varying(20) |dec_dsoins
| date                  | not nulldec_dr_cod   | numeric(11,0)         | not nulldec_mntsso   | numeric(12,6)
|etar_gmu_cod| character varying(6)  |dec_mntmut   | numeric(12,6)         |dec_cli_cod  | numeric(6,0)          |
 
Indexes: dec_cod_mdecs_key,        dec_dr_cod_mdecs_key
Primary key: mdecs_pkey

                 Table "mldecs"   Column     |         Type          | Modifiers
---------------+-----------------------+-----------ldec_dec_cod  | character varying(20) | not nullldec_numlig   |
charactervarying(2)  | not nullldec_nexec    | character varying(8)  |ldec_act_clas | character varying(3)  | not
nullldec_mntpaye | numeric(12,6)         |ldec_pu       | numeric(12,6)         | not nullldec_txsso    | numeric(5,2)
       | not nullldec_mntsso   | numeric(12,6)         | not nullldec_txmut    | numeric(5,2)          |ldec_mntmut   |
numeric(12,6)        |ldec_mnttot   | numeric(12,6)         | not null
 
Indexes: idx_ldec_numlig, (on ldec_dec_cod and ldec_numlig)        ldec_dec_cod_mldecs_key (on ldec_dec_cod)
Primary key: mldecs_pkey
-------------------------
When I make : (case 1)
explain select * from mldecs where ldec_dec_cod = 'x' I obtain :
NOTICE:  QUERY PLAN:
Index Scan using ldec_dec_cod_mldecs_key on mldecs  (cost=0.00..34449.16
rows=8792 width=155)
EXPLAIN

When i make (case 2)
explain select * from mldecs where ldec_dec_cod in  ('x','y') I obtain :
NOTICE:  QUERY PLAN:
Seq Scan on mldecs  (cost=0.00..63928.99 rows=17540 width=155)
EXPLAIN

When i make : (case 3)
explain select * from mclis,mdrs,mdecs,mldecs
where cli_cod = 147854
and dr_cli_cod = cli_cod
and dec_dr_cod = dr_cod
and ldec_dec_cod = dec_cod;

I obtain :
NOTICE:  QUERY PLAN:
Hash Join  (cost=418.82..64348.03 rows=18 width=393) ->  Seq Scan on mldecs  (cost=0.00..55136.99 rows=1758399
width=155)->  Hash  (cost=418.79..418.79 rows=12 width=238)       ->  Nested Loop  (cost=0.00..418.79 rows=12
width=238)            ->  Nested Loop  (cost=0.00..123.31 rows=8 width=150)                   ->  Index Scan using
cli_cod_mclis_keyon mclis
 
(cost=0.00..6.01 rows=1 width=73)                   ->  Index Scan using dr_cli_cod_mdrs_key on mdrs
(cost=0.00..116.93 rows=30 width=77)             ->  Index Scan using dec_dr_cod_mdecs_key on mdecs
(cost=0.00..34.97 rows=8 width=88)

EXPLAIN

I don't understanf why it makes a seq scan on table mldecs on cases 2 and 3,
according that mldecs(ldec_dec_cod) has an index (named
ldec_dec_cod_mldecs_key). The base is vacuumed and analyzed every night. How
can I use the ldec_dec_cod_mldecs_key index on mldecs ?

Regards.
Stephane.





Re: some questions

From
Roberto Mello
Date:
On Wed, May 15, 2002 at 12:49:31PM +0800, Ang Tun Chek wrote:
> hi
>    i am new to postresql and i need some help for some sql command
> 
>    what is the sql command in postresql equal to "describe table"?
> 
>    what is the sql command to list all tables in the database?

Following up on what has already been said, try also "\?" in psql. Also
read the documentation for psql and the User's Guide.

-Roberto

-- 
+----| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |------+ Roberto Mello - Computer Science, USU -
http://www.brasileiro.net/      http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer    
 
Ex astra, scientia.


Re: Help on indexes

From
"Josh Berkus"
Date:
Stephane,

> I don't understanf why it makes a seq scan on table mldecs on cases 2
> and 3,
> according that mldecs(ldec_dec_cod) has an index (named
> ldec_dec_cod_mldecs_key). The base is vacuumed and analyzed every
> night. How
> can I use the ldec_dec_cod_mldecs_key index on mldecs ?

First question: Have you actually measured the execution time of the
various queries?  There are many cases where a Seq Scan is faster than
an Index Scan.  For example, if in Case2, 50% of the entries were
values 'X' of 'Y', then a Seq Scan is the better approach.

Please determine that you actually have a problem with slow execution.Otherwise, the query parser is just doing its
job.

-Josh Berkus