PostgreSQL – Understanding the Output of \d Command

ddlforeign keyindexpostgresqlpsql

Say I run the following command:

\d mtb.big_table

I get the output shown below. My questions are:

1. What are column modifiers? (what does default now() mean?)
2. Under Indexes, Why do some indices refer to multiple columns? (I thought columns can only refer to one column)
3. What is the relationship between Foreign-key constraints when it talks about REFERENCES and the next group below that says "Referenced by" ? (why are there only two Foreign-key constraints and so many TABLE references?).

For example, the following entry:

TABLE "dt.table_segment_hierarchy" CONSTRAINT "dx_segment_hierarchy_ancestor_segment_uid_fkey" FOREIGN KEY (ancestor_segment_uid) REFERENCES dt.table_segment(object_uid)

reads:

TABLE <table_1> CONSTRAINT <column_1> FOREIGN_KEY (<column_2>) REFERENCES <table_2>(<column_3>)

What is the relationship between these tables and columns?

---

More generally, where can I find more information about the formatting of the output of the \d command?

                                                   Table "dt.table_segment"
         Column         |            Type             |                              Modifiers                              
------------------------+-----------------------------+---------------------------------------------------------------------
 segment_id             | integer                     | not null default nextval('dt.table_segment2_segment_id_seq'::regclass)
 segment_type           | character varying(31)       | not null
 object_uid             | character varying(255)      | not null
 object_version         | timestamp without time zone | default now()
 name                   | character varying(255)      | not null
 is_active              | boolean                     | not null
 expiration             | integer                     | 
 expiration_unit        | character varying(255)      | 
 frequency_count        | integer                     | 
 matching_expression    | character varying(32672)    | 
 data_vendor_uid        | character varying(255)      | not null
 creation_date          | timestamp without time zone | not null default now()
 researcher_uid         | character varying           | 
 sharing_enabled        | boolean                     | not null default false
 compressed_id          | integer                     | 
Indexes:
    "dx_segment_pkey" PRIMARY KEY, btree (object_uid)
Foreign-key constraints:
    "dx_segment_researcher_fk" FOREIGN KEY (researcher_uid) REFERENCES dt.table_researcher(object_uid)
    "fkeef87e8854520518" FOREIGN KEY (data_vendor_uid) REFERENCES dt.table_data_vendor(object_uid)
Referenced by:
    TABLE "dt.table_segment_hierarchy" CONSTRAINT "dx_segment_hierarchy_ancestor_segment_uid_fkey" FOREIGN KEY (ancestor_segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "dt.table_segment_hierarchy" CONSTRAINT "dx_segment_hierarchy_descendant_segment_uid_fkey" FOREIGN KEY (descendant_segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "dt.table_shared_organization_segments" CONSTRAINT "dx_shared_organization_segments_segment_uid_fkey" FOREIGN KEY (segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "dt.table_segment_external_id" CONSTRAINT "fk3f8c6d84c87bfc0" FOREIGN KEY (segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "dt.table_segment_group_rate" CONSTRAINT "fk710100c4a72a47f" FOREIGN KEY (segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "dt.table_segment_group_rate" CONSTRAINT "fk710100c4b8416291" FOREIGN KEY (group_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "dt.table_action" CONSTRAINT "fk7c000342a72a47f" FOREIGN KEY (segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "dt.table_action" CONSTRAINT "fk7c000342b7946789" FOREIGN KEY (segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "dt.table_segment_edge" CONSTRAINT "fk9c237d147cc05c6a" FOREIGN KEY (parent_segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "dt.table_segment_edge" CONSTRAINT "fk9c237d14a102ddc" FOREIGN KEY (child_segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "dt.table_flight_group" CONSTRAINT "fkbeae4f48b8416291" FOREIGN KEY (group_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_assets_load" CONSTRAINT "ls_assets_load_winning_group_uid_fkey" FOREIGN KEY (winning_group_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_attributions_load" CONSTRAINT "ls_attributions_load_winning_group_uid_fkey" FOREIGN KEY (winning_group_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_group_converters_load" CONSTRAINT "ls_group_converters_load_group_uid_fkey" FOREIGN KEY (group_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_group_unique_users_load" CONSTRAINT "ls_group_unique_users_load_group_uid_fkey" FOREIGN KEY (group_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_campaign_segment_load" CONSTRAINT "ls_campaign_segment_load_segment_uid_fkey" FOREIGN KEY (segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_creative_exchange_hourly_load" CONSTRAINT "ls_creative_exchange_hourly_load_group_uid_fkey" FOREIGN KEY (group_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_impressions_load" CONSTRAINT "ls_impressions_load_winning_group_uid_fkey" FOREIGN KEY (winning_group_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_related_segment_load" CONSTRAINT "ls_related_segment_load_related_segment_uid_fkey" FOREIGN KEY (related_segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_related_segment_load" CONSTRAINT "ls_related_segment_load_segment_uid_fkey" FOREIGN KEY (segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_segment_load" CONSTRAINT "ls_segment_load_segment_uid_fkey" FOREIGN KEY (segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_top_interest_load_cant_modify" CONSTRAINT "ls_top_interest_load_segment_uid_fkey" FOREIGN KEY (segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_top_interest_load" CONSTRAINT "ls_top_interest_load_segment_uid_fkey1" FOREIGN KEY (segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_winning_segment_hourly_load" CONSTRAINT "ls_winning_segment_hourly_load_winning_group_uid_fkey" FOREIGN KEY (winning_group_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_winning_segment_hourly_load" CONSTRAINT "ls_winning_segment_hourly_load_winning_segment_uid_fkey" FOREIGN KEY (winning_segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_winning_segment_list_segments" CONSTRAINT "ls_winning_segment_list_segments_segment_uid_fkey" FOREIGN KEY (segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_winning_segment_aggregate_data" CONSTRAINT "winning_segment_aggregate_group_fk" FOREIGN KEY (winning_group_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_winning_segment_aggregate_data" CONSTRAINT "winning_segment_aggregate_segment_fk" FOREIGN KEY (winning_segment_uid) REFERENCES dt.table_segment(object_uid)

Best Answer

re 1: that's the default value you define when you create the table:

create table foo 
(
  id integer default 42,
  last_modified timestamp default now()
)

Details are in the manual:

• http://www.postgresql.org/docs/current/static/ddl-default.html
• http://www.postgresql.org/docs/current/static/sql-createtable.html

re 2: an index can be defined on multiple columns:

create table foo
(
  id_1 integer not null, 
  id_2 integer not null
);
create unique index on foo (id_1, id_2);

Again details are in the manual:

• http://www.postgresql.org/docs/current/static/indexes.html
• http://www.postgresql.org/docs/current/static/indexes-multicolumn.html
• http://www.postgresql.org/docs/current/static/sql-createindex.html

Related Solutions

Postgresql – Postgres multiple joins slow query, how to store default child record

You write:

Each customer can have multiple sites, but only one should be displayed in this list.

Yet, your query retrieves all rows. That would be a point to optimize. But you also do not define which site is to be picked.

Either way, it does not matter much here. Your EXPLAIN shows only 5026 rows for the site scan (5018 for the customer scan). So hardly any customer actually has more than one site. Did you ANALYZE your tables before running EXPLAIN?

From the numbers I see in your EXPLAIN, indexes will give you nothing for this query. Sequential table scans will be the fastest possible way. Half a second is rather slow for 5000 rows, though. Maybe your database needs some general performance tuning?

Maybe the query itself is faster, but "half a second" includes network transfer? EXPLAIN ANALYZE would tell us more.

If this query is your bottleneck, I would suggest you implement a materialized view.

---

After you provided more information I find that my diagnosis pretty much holds.

The query itself needs 27 ms. Not much of a problem there. "Half a second" was the kind of misunderstanding I had suspected. The slow part is the network transfer (plus ssh encoding / decoding, possibly rendering). You should only retrieve 100 rows, that would solve most of it, even if it means to execute the whole query every time.

If you go the route with a materialized view like I proposed you could add a serial number without gaps to the table plus index on it - by adding a column row_number() OVER (<your sort citeria here>) AS mv_id.

Then you can query:

SELECT *
FROM   materialized_view
WHERE  mv_id >= 2700
AND    mv_id <  2800;

This will perform very fast. LIMIT / OFFSET cannot compete, that needs to compute the whole table before it can sort and pick 100 rows.

---

pgAdmin timing

When you execute a query from the query tool, the message pane shows something like:

Total query runtime: 62 ms.

And the status line shows the same time. I quote pgAdmin help about that:

The status line will show how long the last query took to complete. If a dataset was returned, not only the elapsed time for server execution is displayed, but also the time to retrieve the data from the server to the Data Output page.

If you want to see the time on the server you need to use SQL EXPLAIN ANALYZE or the built in Shift + F7keyboard shortcut or Query -> Explain analyze. Then, at the bottom of the explain output you get something like this:

Total runtime: 0.269 ms

MySQL – Foreign Key Constraint Fails

You need to have already declared the table that the foreign key references, before you can define a foreign key that references it.

Once you declare the second table, you can then declare the first table. Tested here on MySQL 5.5.27.

If you need to bypass the validation, you can do this:

SET FOREIGN_KEY_CHECKS = 0;
-- declare tables
SET FOREIGN_KEY_CHECKS = 1;

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_foreign_key_checks

Related Question

• Postgresql – Improve performance on concurrent UPDATEs for a timestamp column in Postgres
• PostgreSQL – Improve Full-Text Search Performance with Accurate Row Estimates
• Postgresql – use `on delete set null` on a compound foreign key which contains a primary key column
• PostgreSQL – How to Delete Duplicate Records Efficiently
• PostgreSQL – Automatic Index Creation for Primary vs. Foreign Keys
• MySQL and PostgreSQL – Practical Use Cases for REFERENCES Privilege
• PostgreSQL – Finding Most Frequent Values for a Given Column
• PostgreSQL 9.5 – Query Performance Impact of JOINed Column in SELECT Clause