Re: Query questions - Mailing list pgsql-general

From Poul Jensen
Subject Re: Query questions
Date
Msg-id 431AF51C.8070104@gfy.ku.dk
Whole thread Raw
In response to Re: Query questions  (Ragnar Hafstað <gnari@simnet.is>)
Responses Re: Query questions
List pgsql-general
Thank you very much for your response! It leads to another couple of
questions:

>>I'm building a database containing key parameters for ~500,000 data
>>files. The design I found logical is
>>
>>Two tables for each file:
>>1) Larger table with detailed key parameters
>>    (10-15 columns, ~1000 rows), call it large_table
>>2) Small table with file summary
>>    (~30 columns, 1 row), call it small_table
>>
>>
>
>you want to create 1 million tables, all with one of
>2 schemas?
>
>

I started out with a schema for each file, thinking I could utilize the
schema
structure in queries, but I don't see how. Schemas are useful for grouping
tables according to users/owners. Other than that, do they add anything
but a dot in the table name?

>why not just 2 tables, each with the additional "file"
>column ?
>
>
>>...
>>SELECT <large_table columns> FROM <regular expression>
>>     WHERE <condition on large_table>
>>    IF <condition on corresponding small_table>;
>>
>>
>
>this would then be something like:
>
>  SELECT <large_table columns> FROM large_table
>    WHERE file ~ <regular expression>
>    AND <condition on large_table>
>    AND <subquery involving small_table>
>
>

The large_table would have ~500 million rows, each of which would have
to be checked for the first condition (pattern matching above). With
separate
tables there are "only" ~500,000 initial checks to do.
Also, I don't see how to construct that small_table-subquery. If it is
possible
I would love to know how! Can you (or anybody else) give an example?

Thank you,

Poul

pgsql-general by date:

Previous
From: Lukas Demetz
Date:
Subject: Embed PostgreSQL in application
Next
From: Douglas McNaught
Date:
Subject: Re: Query questions