[GENERAL] Cost of max
I build a little test table with columns id serial, a text, b text. Why the cost of max(id) is made as a seq scan, as is for count(id)? This is a problem with very large tables. IMHO, select max(id) could be done using the index prova_id_key. Your opinion? TIA. Stefano mydb=> explain select max(id) from prova; NOTICE: QUERY PLAN: Aggregate (cost=43.00 rows=1000 width=4) -> Seq Scan on prova (cost=43.00 rows=1000 width=4) EXPLAIN mydb=> explain select count(id) from prova; NOTICE: QUERY PLAN: Aggregate (cost=43.00 rows=1000 width=4) -> Seq Scan on prova (cost=43.00 rows=1000 width=4) -- Dott. Stefano Bargioni Biblioteca della Pontificia Universita' della Santa Croce - Roma <mailto:[EMAIL PROTECTED]> <http://www.usc.urbe.it> Personal web page:<http://www.usc.urbe.it/html/php.script?bargioni> --- "Si apud bibliothecam hortulum habes, nihil deerit" (Cicerone) ---
[GENERAL] Re: Very slow select
> Stefano Bargioni writes:
> > SELECT FROM t WHERE a='my string'; works very fast. The same select
> > made on b is very slow (2 or 3 seconds), as if no index exists for
> > column b.
>
> What does EXPLAIN say about the two queries?
>
> regards, tom lane
>
Dear Tom, thanks for your help. Here is the explain results.
As you note, the second select is not index driven. This is why it is slow. Are
indexes built correctly / used / when fields contain very large text strings? Can an
hash index be useful?
Bye (Merry 2K Xmas!). Stefano
ucc2=> explain select count(*) from document where xtitle='a';
NOTICE: QUERY PLAN:
Aggregate (cost=70.61 rows=759 width=4)
-> Index Scan using xtitle_index on document (cost=70.61 rows=759 width=4)
EXPLAIN
ucc2=> explain select count(*) from document where xothertitles='a';
NOTICE: QUERY PLAN:
Aggregate (cost=26005.44 rows=287195 width=4)
-> Seq Scan on document (cost=26005.44 rows=287195 width=4)
EXPLAIN
ucc2=> \d document
Table= document
+--+--+---+
| Field | Type| Length|
+--+--+---+
| id | int4 not null default nextval('" | 4 |
| title| text | var |
| xtitle | text | var |
| othertitles | text | var |
| xothertitles | text | var |
| author | text | var |
| xauthor | text | var |
| otherauthors | text | var |
| xotherauthors| text | var |
| subject | text | var |
| xsubject | text | var |
| publisher| text | var |
| xpublisher | text | var |
| collection | text | var |
| xcollection | text | var |
| yearpub | int4 | 4 |
| xyearpub | int4 | 4 |
| locationpub | text | var |
| xlocationpub | text | var |
| languagecode | text | var |
| classification | text | var |
| digest | text | var |
+--+--+---+
Indices: digest_document_index
document_id_key
xauthor_index
xcollection_index
xotherauthors_index
xothertitles_index
xpublisher_index
xsubject_index
xtitle_index
indexes where created with
create index xothertitles_index on document (xothertitles);
create index xtitle_index on document (xtitle);
--
Dott. Stefano Bargioni
Biblioteca della Pontificia Universita' della Santa Croce - Roma
<mailto:[EMAIL PROTECTED]> <http://www.usc.urbe.it>
Personal web page:<http://www.usc.urbe.it/html/php.script?bargioni>
--- "Si apud bibliothecam hortulum habes, nihil deerit" (Cicerone) ---
[GENERAL] Very slow select
I defined a table with two indexed text columns, a and b, of variable length. This table contains 287195 rows. SELECT FROM t WHERE a='my string'; works very fast. The same select made on b is very slow (2 or 3 seconds), as if no index exists for column b. I'd like to know why. Note that b contains very long strings, more than 500 chars. Is there a limit in indexing? Thanks. Stefano -- Dott. Stefano Bargioni Biblioteca della Pontificia Universita' della Santa Croce - Roma <mailto:[EMAIL PROTECTED]> <http://www.usc.urbe.it> Personal web page:<http://www.usc.urbe.it/html/php.script?bargioni> --- "Si apud bibliothecam hortulum habes, nihil deerit" (Cicerone) ---
[GENERAL] Indexing arrays
I'd like to index an array. This is my table: template1=> \d test_array Table= test_array +--+--+---+ | Field | Type| Length| +--+--+---+ | name | text | var | | pay_by_quarter | int4[] | var | | schedule | text[] | var | +--+--+---+ How can I create an index for schedule? I need to index every occurrence of this array. Thanks. Stefano -- Dott. Stefano Bargioni Biblioteca della Pontificia Universita' della Santa Croce - Roma <mailto:[EMAIL PROTECTED]> <http://www.usc.urbe.it> Personal web page:<http://www.usc.urbe.it/html/php.script?bargioni> --- "Si apud bibliothecam hortulum habes, nihil deerit" (Cicerone) ---
Re: [GENERAL] Alternate location
Peter Eisentraut wrote: > On Wed, 22 Mar 2000, Stefano Bargioni wrote: > > > $ PGDATA2=/data > > $ initlocation $PGDATA2 > > initlocation PGDATA2 > > > We are initializing the database area with username postgres (uid=40). > > This user will own all the files and must also own the server process. > > $ createdb -D $PGDATA2 ucc2 > > createdb -D PGDATA2 ucc2 > > > ERROR: Unable to locate path '/data/ucc2' > > This may be due to a missing environment variable in the server > > createdb: database creation failed on ucc2. Dear Peter, thank you, the manual has an error: in the initlocation command, the $ must be deleted; but there is still a problem with createdb: $ initlocation PGDATA2 We are initializing the database area with username postgres (uid=40). This user will own all the files and must also own the server process. Creating Postgres database system directory PGDATA2 Creating Postgres database system directory PGDATA2/base $ createdb -D PGDATA2 ucc2 ERROR: Unable to locate path 'PGDATA2/ucc2' This may be due to a missing environment variable in the server createdb: database creation failed on ucc2. Tia. Stefano
[GENERAL] Alternate location
Hi, I need to define a very large db in a disk partition /data that is not the default partition of Postgres. The Chapter 10 of the admin guide describes a procedure that fails (postgres 6.5.3): $ PGDATA2=/data $ initlocation $PGDATA2 We are initializing the database area with username postgres (uid=40). This user will own all the files and must also own the server process. $ createdb -D $PGDATA2 ucc2 ERROR: Unable to locate path '/data/ucc2' This may be due to a missing environment variable in the server createdb: database creation failed on ucc2. Any dea? Thanks a lot. Stefano -- Dott. Stefano Bargioni Biblioteca della Pontificia Universita' della Santa Croce - Roma <mailto:[EMAIL PROTECTED]> <http://www.usc.urbe.it> Personal web page:<http://www.usc.urbe.it/html/php.script?bargioni> --- "Si apud bibliothecam hortulum habes, nihil deerit" (Cicerone) ---
Re: [GENERAL] Problem with LIKE operator
Gabriel Fernandez wrote: > Hi, > > I have some problems using LIKE within strings which have brackets. > > For example if i do: > > select code from codes where code like '(4)' ; > > i do not obtain nothing. But in the DB indeed there is a row whose code > is '(4)'. > > I have tried to escape the brackets with '\\(4\\)' or with ' \(4\)' but > it doesn't work. > > How can i do it ? > > Gabi :-) Try select code from codes where code ~ '(4)' ; HTH. Stefano -- Dott. Stefano Bargioni Biblioteca della Pontificia Universita' della Santa Croce - Roma <mailto:[EMAIL PROTECTED]> <http://www.usc.urbe.it> Personal web page:<http://www.usc.urbe.it/html/php.script?bargioni> --- "Si apud bibliothecam hortulum habes, nihil deerit" (Cicerone) ---
